DEV Community: Milan Parikh

Power App Functions

Milan Parikh — Wed, 03 Sep 2025 21:30:08 +0000

🧠 Mastering Power FX Data Functions: FILTER vs SEARCH vs LOOKUP

Milan Parikh ・ Sep 3

🧠 Mastering Power FX Data Functions: FILTER vs SEARCH vs LOOKUP

Milan Parikh — Wed, 03 Sep 2025 20:28:28 +0000

When building canvas apps in Power Apps, choosing the right data function is critical—not just for app correctness, but also for performance, delegation, and maintainability. Power FX offers three key functions to retrieve data: Filter(), Search(), and LookUp() but many developers confuse their roles.

This post breaks down:

What each function returns

When to use which one

Real-world code examples with comments

Best practices and performance tips

Whether you're a beginner or scaling enterprise apps, this guide will save you hours of trial and error.

🔍 FILTER(): The Multi-Record Finder
✅ What it does:

Returns a table of all matching records.

📌 When to use:

Populating galleries or data tables

Applying multiple conditions

Preserving tabular data structure

Working with relational filtering logic

💻 Examples:

// Basic Filter - Get active US customers
Filter(
  Customers, // Source table
  Status = "Active" && Country = "USA" // Condition: must be active AND from USA
)

// Complex Filter - Orders with preferred vendor items
Filter(
  Orders, // Primary source table
  CountRows( // Count how many order details match our criteria
    Filter(
      OrderDetails, // Related table
      OrderID = Orders.ID && // Join condition: match order IDs
      LookUp(Vendors, ID = ProductVendorID).Preferred = true // Only preferred vendors
    )
  ) > 0 // Final condition: orders must include preferred vendor item
)

🔎 SEARCH(): The Text Hunter
✅ What it does:

Returns a table of records that contain the search string.

📌 When to use:

User-entered search bars

Searching across multiple columns

"Contains" matches instead of exact

When case sensitivity doesn’t matter

💻 Examples:

// Basic Search - Match input across multiple columns
Search(
  Products, // Source table
  SearchInput.Text, // What user typed
  "Title", "Description", "Category" // Columns to search
)

// Advanced: Search + Filter combo
Filter(
  Search(
    Concatenate(Products, DiscontinuedProducts), // Merge active + discontinued
    SearchInput.Text, // User input
    "Name", "Description" // Searchable fields
  ),
  Price <= PriceSlider.Value && IsAvailable = true // Further filter results
)

🟡 Note: Search() is not delegable for many connectors (like SQL or SharePoint). Use carefully with large datasets!

🎯 LOOKUP(): The Single Record Sniper
✅ What it does:

Returns the first record that matches your condition.

📌 When to use:

You only need one record

Creating detail views (e.g., profile screen)

Matching unique fields (e.g., Employee ID)

For performance-critical scenarios

💻 Examples:

// Basic LookUp - Get employee name and email
LookUp(
  Employees, // Table to search
  EmployeeID = varSelectedID, // Match by selected ID
  {Name: Name, Email: Email} // Return only these two fields
)

// Nested LookUp - Get manager of employee’s department
LookUp(
  Managers, // Outer table
  DepartmentID = LookUp(
    Employees, // Inner table
    ID = varEmployeeID // Match employee
  ).DepartmentID, // Use employee’s department
  {Name: FullName, Contact: Phone, Office: Location} // Return these values
)

⚡ LookUp() is fast but only returns one record. If you use it where multiple results are expected, you’ll run into logic bugs.

⚙️ Pro Tips & Pitfalls to Avoid

✅ Best Practices:

Use Filter() when you need a table
Use LookUp() for a single item (like a detail screen)
Store repeated formulas in variables (e.g., Set() or With())
Use Search() only when truly needed by users
Test with real data volumes
Use collections for caching frequently accessed data
Build delegation-friendly formulas from the start

🚫 Common Mistakes:

Using LookUp() when Filter() is required
Nesting too many formulas (performance killer!)
Searching large datasets with Search() (delegation issues)
Not handling nulls/empty results properly
Overcomplicating with non-delegable logic
Forgetting that LookUp() only returns the first record
Using Filter() unnecessarily when a simple LookUp() would be faster

🔚 Conclusion

Choosing between Filter(), Search(), and LookUp() in Power FX is more than a syntax decision—it's a performance, usability, and maintainability strategy.

Mastering these data functions lets you:

Build smarter and scalable apps
Avoid common performance bottlenecks
Enhance user experience with real-time data interactions

💬 What’s your go-to Power FX tip for working with large datasets or dynamic user filters? Drop it in the comments!

Unlocking Intelligent Data Access with Microsoft Fabric Data Agent

Milan Parikh — Fri, 29 Aug 2025 16:10:13 +0000

In today’s data-driven world, organizations face increasing challenges to make complex enterprise data accessible and actionable for all users. Microsoft Fabric’s Data Agent is designed to address this challenge by enabling seamless, natural language interaction with diverse data sources empowering users to extract insights without needing deep technical expertise.

What Is Microsoft Fabric Data Agent?
The Microsoft Fabric Data Agent is a powerful AI-driven interface that allows users to query and interact with enterprise data across multiple Fabric workloads. It integrates data from lakehouses, warehouses, Power BI semantic models, and Kusto Query Language (KQL) databases into a unified experience.

This unified layer enables users to ask questions in natural language and receive answers or generate analytical reports on the fly, dramatically simplifying the data exploration process.

Key Benefits
Democratized Data Access: Users across the organization regardless of their technical background can effortlessly interact with complex datasets.
Unified Data Experience: By bridging multiple Fabric data sources, the Data Agent eliminates data silos and provides consistent insights.
Accelerated Decision Making: Instant, AI-powered responses to natural language queries help business users make informed decisions faster.
Seamless Integration: The agent works natively within Microsoft Fabric, ensuring smooth deployment and operation without additional infrastructure.

Typical Use Cases
Business analysts querying lakehouse data for real-time trends without writing SQL.
Data scientists accessing curated datasets and generating reports through conversational queries.
Executives receive on-demand summaries from Power BI semantic models to monitor KPIs.

Getting Started
Setting up the Fabric Data Agent involves configuring connections to your enterprise data sources and enabling natural language processing capabilities within your Fabric environment. Microsoft provides detailed guidance and tools to assist with deployment and integration.

For a comprehensive walkthrough, visit the official documentation here: Fabric Data Agent Scenario - Microsoft Learn

Conclusion
The Microsoft Fabric Data Agent represents a significant leap toward making enterprise data truly accessible and actionable. By enabling natural language interaction across a broad spectrum of data sources, it empowers organizations to break down barriers between data and decision-making. As Microsoft Fabric continues to evolve, tools like the Data Agent will be essential for unlocking the full value of data in the AI-driven era.