Power BI DAX Filter Table [With Real Examples]

In this Microsoft Power BI Tutorial, we will learn how to filter the table value using the Power BI DAX function. We will also cover the below-mentioned headings with different examples.

I recently worked on the Power Bi report, where I got a requirement to filter table values. To achieve it, I have used the Power BI Dax Filter function.

1. What is the Power BI DAX filter table?
2. How to filter the table by slicer using Power BI DAX
3. How to use Power BI DAX to filter the table by date
4. How can we filter the table between two dates using Power BI Dax?
5. How to filter a table with multiple values in Power BI DAX
6. How to use Power BI DAX filter table based on column value
7. Filter table based on another table using the power BI DAX

Power BI DAX filter table

Let us see how we can filter the table value based on the filter condition applied using Power BI Dax in Power BI.

In this example, I have used the HR Details Sample data below, which contains information about the Employees. Here we will filter the table data based on the Employee Gender.

• Initially, open the power bi desktop and load the data into it, click on the new measure option from the ribbon and apply the below formula to calculate the Employees Count.

Count Values = COUNT('HR Details'[EmpId])

Where,

1. Count Values = Measure Name
2. HR Details = Table Name
3. EmpId = Column Name

• Now select the New measure option to filter the table data using the Power BI Dax filter function and apply the below formula into it.

Filter Table data = CALCULATE([Count Values],FILTER('HR Details','HR Details'[Gender]="Female"))

Where,

1. Filter Table data = Measure Name
2. HR Details = Table Name
3. Gender = Column Name

• Now in the report section, select the two table visuals from the visualizations.
• In the table visual, drag and drop the EmpId, Emp Name, and Gender fields from the fields pane.
• In another table visually drag and drop the EmpId, Emp Name, Gender, and the created measure value to filter the table data.
• The screenshot below shows that the table visually filters the data and displays it based on the applied condition.

This is how to filter the table value based on the filter condition applied using Power BI Dax in Power BI.

Also read: How To Remove Filter From Power BI DAX

Power BI DAX filter table by slicer

Let us see how we can filter the table value based on the slicer visual using the Power BI Dax filter in Power BI.

In this example, we will use the slicer visual to filter the table data for the employee who is working part-time and full-time.

• Open the power bi desktop and load the data into it, and select the slicer visual from the visualizations.
• In the slicer visual, drag and drop the Emptype fields from the fields pane as shown below:

• Now select the New measure option to filter the table data using the Power BI Dax filter function and apply the below formula into it.

Filter data = CALCULATE([Count Values],FILTER('HR Details','HR Details'[EmpType]="Full-Time"))

Where,

1. Filter data = Measure Name
2. HR Details = Table Name
3. EmpType = Column Name

• Now select the table visual from the visualizations, drag and drop the EmpId, Emp Name, EmpType, Gender, and the created measure value to filter the table data.
• The screenshot below shows that the table visually filters the data and displays it based on the applied condition.

• From the visual filter, when we select the Part-time as Emp type we can see that based on the condition it filters the table data value as below:

This is how to filter the table value based on the slicer visual using Power BI Dax in Power BI.

Check out: How to Filter Blank Value in Power BI

Power BI DAX filter table by date

Let us see how we can filter table values based on the date using the Power BI Dax filter function in Power Bi.

In this example, I am going to filter table values based on a specific date that is passed on the Power Bi Dax filter formula.

• Initially, open the power bi desktop and load the data into it, click on the new measure option from the ribbon and apply the below formula to calculate the Employees Count.

Date = 
VAR DateStart =
    DATE ( 2021,7,18 )
RETURN
    CALCULATE (
        [Count Values],
        FILTER ( 'HR Details', 'HR Details'[Date of Joining].[Date]= DateStart )
    )

Where,

1. DateStart = Variable Name
2. Count Values = Measure Name
3. HR Details = Table Name
4. Date of Joining = Column Name

• Now in the report section, select the two table visuals from the visualizations.
• In the table visual, drag and drop the EmpId, Emp Name, Gender, and Date of Joining fields from the fields pane.
• In another table visually drag and drop the EmpId, Emp Name, Gender, Date of Joining, and the created measure value to filter the table data.
• The screenshot below shows that the table visually filters the data and displays it based on the applied condition.

This is how to filter table values based on the date using the Power BI Dax filter function in Power Bi.

Have a look: Power Query Add Column Date [15 Examples]

Power BI DAX filter table between two dates

Let us see how we can filter table values based on the two between dates using the Power BI Dax filter function in Power Bi.

In this example, I am going to filter table values based on two specific dates that are passed on the Power Bi Dax filter formula.

• Open the power bi desktop and load the data into it, click on the new measure option from the ribbon and apply the below formula to calculate the Employees Count.

Between Two Dates = 
CALCULATE(
    'HR Details'[Count Values],
    DATESBETWEEN ( 'HR Details'[Date of Joining], DATE(2021,4,1), DATE(2022,1,22) )
)

Where,

1. Between Two Dates = Measure Name
2. HR Details = Table Name
3. Date of Joining = Column Name

• Now in the report section, select the two table visuals from the visualizations.
• In the table visual, drag and drop the EmpId, Emp Name, Gender, and Date of Joining fields from the fields pane.
• In another table visually drag and drop the EmpId, Emp Name, Gender, Date of Joining, and the created measure value to filter the table data.
• The screenshot below shows that the table visually filters the data and displays it based on the applied condition.

This is how to filter table values based on the two between dates using the Power BI Dax filter function in Power Bi.

Check: Power Query Add Column If Statement

Power BI DAX filter table multiple values

Let us see how we can filter a table with multiple values using the Power BI Dax filter function in Power Bi.

In this example, we will see filter multiple table values using the Power Bi Dax filter formula.

• Initially, open the power bi desktop and load the data into it, click on the new measure option from the ribbon and apply the below formula to calculate the Employees Count.

Filter data = CALCULATE([Count Values],FILTER('HR Details','HR Details'[EmpType]="Full-Time" && 'HR Details'[Gender]="Male"))

Where,

1. Filter data = Measure Name
2. HR Details = Table Name
3. EmpType, Gender = Column Name

• Now in the report section, select the two table visuals from the visualizations.
• In the table visual, drag and drop the EmpId, Emp Name, Gender, and Date of Joining fields from the fields pane.
• In another table visually drag and drop the EmpId, Emp Name, Gender, Date of Joining, and the created measure value to filter the table data.
• The screenshot below shows that the table visually filters the data and displays it based on the applied condition.

This is how to filter a table with multiple values using the Power BI Dax filter function in Power Bi.

Read out: How to duplicate multiple columns using Power Query

Power BI DAX filter table based on column value

Let us see how we can filter table values based on column values using the Power BI Dax filter function in Power Bi.

In this example, I am going to filter table values based on a column value and display the filtered data value in the table visual.

• Open the power bi desktop and load the data into it, click on the new measure option from the ribbon and apply the below formula to calculate the Employees Count based on the column value.

Based on column value = 
VAR selectedids = VALUES(Slicer[Gender])
VAR myType= CALCULATE([Count Values],FILTER('HR Details','HR Details'[Gender]=selectedids))
RETURN
myType

Where,

1. Based on column value = Measure Name
2. Slicer, HR Details = Table Name
3. EmpType, Gender = Column Name

• Now in the report section, select the Slicer visual and table visual from the visualizations.
• In the slicer visual, drag and drop the Gender field value from the slicer table.
• Now in the table visually drag and drop the EmpId, Emp Name, EmpType, Gender, and the created measure value to filter the table data.
• The screenshot below shows that the table visually filters the data and displays it based on the applied condition.

• In the same way, when we select another option from the Slicer visual it filters and displays the table data accordingly:

This is how to filter table values based on column values using the Power BI Dax filter function in Power Bi.

Also check: How to add an empty column in Power BI

Power BI DAX filter table based on another table

Here we will see how we can filter table values based on another table using the Power BI Dax filter function in Power Bi.

In this example, I have used two tables as highlighted below, to filter and concatenate the values.

Table 1:

Table 2:

• Open the power bi desktop and load the data into it, And make sure both the table has relationships, else the table visually displays an error value.
• click on the new measure option from the ribbon and apply the below formula to filter and concatenate two tables’ values.

Concatenate = 

CONCATENATEX(
    FILTER(
        'HR Details',
        'HR Details'[Count Values]=COUNT('HR Details'[EmpId])
    ),
    RELATED( 'Slicer Table'[Gender] ),
    ", "
)

Where,

1. Concatenate = Measure Name
2. Slicer Table, HR Details = Table Name
3. EmpId, Gender = Column Name

• Now in the report section, select the table visual from the visualizations drag and drop the EmpId, Emp Name, EmpType, Gender, and the created measure value to filter the table data.
• The screenshot below shows that the table visually filters and concatenates the data and displays it based on the applied condition.

This is how to filter table values based on another table using the Power BI Dax filter function in Power Bi.

This is how to filter table values using the Power BI DAX filter function in Power BI, Also examined the below-mentioned topics in this Power Bi tutorial.

• Power BI DAX filter table
• Power BI DAX filter table by slicer
• Power BI DAX filter table by date
• Power BI DAX filter table between two dates
• Power BI DAX filter table multiple tables
• Power BI DAX filter table based on column value
• Power BI DAX filter table based on another table

Furthermore, you may like some more Power BI Tutorials:

• How to add a column with the same value in Power BI
• Power BI DAX Max Date [With 15+ Real Examples]
• How to add a column with a dropdown list in Power Query
• How to add column from another table in Power BI [3 Different ways]
• How to add column with a fixed value in Power BI
• Power BI DAX Calendar Function