DAX Filters Function Simplified for Stunning Dashboards

The DAX filters function in Power BI allows a user to filter data based on specific criteria. Users can use the function to extract a subset of data from a larger dataset. This way, the user will be able to focus on the information that is most relevant to the analysis.

This guide will help you figure out what the DAX filters function is, why the DAX filters function is important, and what the purpose of the DAX filters function in Power BI is.

Table of Contents:

1. What are DAX Filters Functions?
2. Why is the DAX Filters Function Important?
3. How to Implement the DAX filters function in Power BI?
4. How to Design Visualizations in Power BI?
5. Wrap Up

First…

What are DAX Filters Functions?

Here are the major DAX filters functions:

• Filter Function: It’s used to filter a table (or expression) to include only the rows that satisfy specific conditions.
  • FILTER (Table, Condition)
• ALL Function: It removes all filters from a table (or specified columns) in a context.
  • ALL(Table/Column)
• ALLEXCEPT Function: The Power BI ALLEXCEPT function removes all filters from a table except those explicitly specified within the function, allowing for focused data analysis while retaining essential filter context.
  • ALLEXCEPT (Table, Column1, Column2, …)
• VALUES Function: It returns a one-column table that contains the unique values from a specified column. Existing filters are considered during this process.
  • VALUES(Column)
• CALCULATETABLE Function: The CALCULATE function in Power BI evaluates a table expression with specified filters, enabling precise control over the data being analyzed.
  • CALCULATETABLE (Table, Filter1, Filter2, …)

Why is the DAX Filters Function Important?

The DAX Filters function is important in Power BI and other tools leveraging the Data Analysis Expressions (DAX) language because it plays a critical role in refining and customizing data analysis. Here’s why the Filters function matters:

• Filtering Data Tables: The FILTER function filters rows from a table (or a table expression) based on specified conditions. This allows the user to focus on a subset of data that meets specific criteria.
• Aggregations and Totals: FILTER can be combined with other DAX functions to work with aggregated data effectively. By applying filters, you can control which rows contribute to the calculations, enabling you to generate precise and desired results.
• Advanced Data Modeling: During advanced data modeling in the Power BI data model, the FILTER function is instrumental in specifying criteria for table joins and extracting relevant information efficiently.
• Time Intelligence: FILTER is used in time intelligence calculations to define date ranges and periods for calculations such as year-to-date, or month-to-date.

How to Implement the DAX filters function in Power BI?

In this section, you’ll discover the syntax of the filter function.

Syntax of Filter Function

• FILTER(table, filter_condition)
• table: The table to be filtered. The table can be an expression that results in a table.

filter

• filter_condition: A Boolean expression that is to be evaluated for each row of the table. For instance, [Amount] > 0 or [Region] = “France”

Here’s how to implement the FILTER function in Power BI:

• Launch Power BI and load the relevant dataset into the workspace.
• Identify the data table(s) you want to analyze and apply scenario analysis to.
• Open the Power Query Editor to transform and refine the data if needed.
• Create a new measure or column where you plan to implement the FILTER DAX function.
• Within the formula bar, enter the FILTER function, and specify the table (or column) you want to filter.
• Define the filtering condition using logical operators, comparison operators, and other appropriate DAX functions.
• Preview and validate the result of the FILTER DAX function to ensure it selects the desired data.
• Apply the FILTER DAX function within the data visualizations (or calculations) to perform scenario analysis.

Example

In the example, you’ll notice the creation of a Power BI report of Internet sales outside the United States using a measure that filters out sales in the US. There’s slicing by calendar year and product categories. To create the measure, you’ll filter the table, Internet Sales USD, by using Sales Territory. After that, use the filtered table in a SUMX function.

The expression for the example is:

FILTER(’InternetSales USD’, RELATED(’SalesTerritory’[SalesTerritoryCountry])<>”United States”)

Return the table that is a subset of Internet Sales minus all rows that belong to the United States sales territory. The RELATED function links the Territory Key in the Internet Sales table to SalesTerritoryCountry in the SalesTerritory table.

The table below shows a proof of concept for the measure, NON-USA Internet Sales, the formula is provided in the code section. The table compares all Internet sales with non-USA Internet sales, and it shows that the filter expression works, by excluding the US from the computation.

To recreate the table, add the field, SalesTerritoryCountry, to the Row Labels area of the report or PivotTable.

Row Labels	Internet Sales	Non-USA Internet Sales
Australia	$4,999,021.84	$4,999,021.84
Canada	$1,343,109.10	$1,343,109.10
France	$2,490,944.57	$2,490,944.57
Germany	$2,775,195.60	$2,775,195.60
United Kingdom	$5,057,076.55	$5,057,076.55
United States	$9,389,479.79	0
Grand Total	$26,054,827.45	$16,665,347.67

How to Design Visualizations in Power BI?

Stage 1: Log into Power BI, enter your email, and click “Submit.”

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Create a Data Set and Select the Data Set to Use in the Sankey Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

• Paste the data table above into the “Power Query” window. Select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set list. If no data set has been created, you’ll get an error message.

• Choose the data set you want to use for the creation of the Sankey diagram. After that, Power BI will populate the screen as shown below.

• Click on the “Create a report” dropdown, and select “Start from scratch.”

• A Report Canvas screen appears as shown below.

Stage 3: Add the Power BI Sankey Diagram Extension by ChartExpo

• To create the Sankey Diagram, you’ll have to use an add-in or Power BI visual from AppSource. Navigate to the right side of the Power BI dashboard, and open the Power BI Visualizations panel. Click the ellipsis symbol (…) to import the Power BI Sankey Diagram extension by ChartExpo.

• In the following menu that opens, select the “Get more visuals” option.

• Enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box. You’ll see the “Sankey Diagram for Power BI by ChartExpo” as shown below.

• Click the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

Stage 4: Draw a Sankey Diagram with ChartExpo’s Power BI extension

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel. After that, a report section similar to the one below will be displayed on your dashboard.

• There’s the option to resize the visual. Navigate to the right side of the Power BI dashboard. You’ll see “Fields” next to “Visualizations.”

• Follow the sequence below when selecting the fields in the Sankey chart.
  • Total Cost
  • Company Type
  • Company Name
  • Expertise Categories
  • Expertise
  • Cost

• You’ll have to provide your email address or a ChartExpo license key.

Stage 5: Apply a Subscription Key or Activate the ChartExpo Trial.

• Select the ChartExpo visual to see three icons below “Build Visual” in the Visualizations panel.

• Select the middle icon, “Format visual.” After that, the visual properties will be populated.

• As a new user, you’ll have to enter your email address in the textbox under the “Trial Mode” section. Toggle “Enable Trial” to activate the 7-day trial.

• The Sankey Diagram created with the 7-day trial comes with the ChartExpo watermark.

• If you have a license key, enter it in the “ChartExpo License Key” textbox in the “License Settings” section. Slide the toggle switch next to “Enable License” to “On.”

• The Sankey diagram doesn’t come with a watermark.

• To create 2 measures using the DAX filters function, you’ll have to filter the records on the “Company Type” column in the table.
• SubcontractorCost = SUMX(FILTER( ’Civil Work’, ‘Civil Work’[Company Type]==”Subcontractor”), ‘Civil Work’[Cost])
• SupplierCost = SUMX(FILTER( ‘Civil Work’, ‘Civil Work’[Company Type]==”Supplier”), ‘Civil Work’[Cost])
• After creating the measures, the data model will look like the image below.

• These measures can be used one by one to visualize the behavior of a chart with filtered records. Select columns in the sequence:
• • Total Cost
  • Company Type
  • Company Name
  • Expertise Categories
  • Expertise
  • SubcontractorCost

• You’ll notice that the cost is showing only for sub-contractors.

• You’ll have to unselect the SubcontractorCost measure and select the SupplierCost measure.

• From the chart below, you’ll notice cost shows only for suppliers.

• You have seen how visualization changes when you use measures created using the DAX filters function. Moving on, let’s continue with the original (unfiltered measure) cost and explore chart properties.
• To add a prefix (like the $ sign) with the numeric values in the chart, expand the “Stats” properties and include the Prefix value.

• To add colors to each node, expand the “Level Colors” properties and select the colors.

• The changes will be automatically saved.

Insights

• At Level 1 (Total Cost), the procurement cost is $155K.
• At Level 2 (Company Type), out of the $155K cost, $82.4K (53.3%) was spent on subcontractors, while $72.3K (46.7%) was allocated to the supplier.
• At Level 3 (Company Name), the supplier cost of $72.3K was divided between two companies: Five-star Construction and Power-up Builder, with charges of $19.3K and $53.0K, respectively.
• The subcontractor cost of $82.4K was distributed among three companies: Onyx General Contractors, Skyline Contractors, and Living Well Remodeling. They charged $30.7K, $25.6K, and $26.0K, respectively, for their services.

Steps to Analyze Data Using DAX Filters Function in Power BI Charts:

1. Open your Power BI Desktop or Web.
2. From the Power BI Visualizations pane, expand three dots at the bottom and select “Get more visuals”.
3. Search for “Sankey Chart by ChartExpo” on the AppSource.
4. Add the custom visual.
5. Select your data and configure the chart settings to create the chart.
6. Customize your chart properties to add header, axis, legends, and other required information.
7. Share the chart with your audience.

The following video will help you create a Sankey Chart in Microsoft Power BI.

FAQs

How do you use filter selection in a DAX calculation?

The DAX filters function is used to select and apply specific criteria (or conditions). It gives room for tailored analyses based on the user-defined filters (or slicer selections) in Power BI.

Is filter an iterator function in DAX?

No, the FILTER function in DAX is not an iterator. It’s a table function that filters data based on specified conditions.

How do I filter a column value in DAX?

You’ll have to use functions like FILTER or CALCULATETABLE.

Wrap Up

The DAX function plays a major role in specifying conditions for filter data, and that enables dynamic calculations and analysis. It shapes the calculation context and allows for targeted and meaningful data manipulation in Power BI.

One of the major importance of the DAX filters function is row-level security. With the DAX filters function, the user will easily define security filters to restrict data access based on the permissions and roles of the user.

The DAX filters function also comes in handy when performing complex calculations. It’s a great fit for complex calculations that involve multiple criteria or conditions. With the DAX filters function, users can create sophisticated expressions to handle various scenarios.

Now you know how the DAX filters function works, how will you integrate it into your data analysis?