Dynamic Filtering Data Using Date Slicers and DAX Queries

[Hashmeet]

Hi Everyone,

I’m facing a challenge in Power BI with dynamic filtering using date slicers and DAX queries.

Here's the scenario:

Data Setup:

I have a table named Raw_Data with a column Date Extracted.

Based on this, I created two date filter tables (slicers):

• First Date Filter: Extracts all distinct dates from Raw_Data.
• Second Date Filter: Extracts all distinct dates except the latest one.

DAX Tables:
I created two filtered tables using DAX queries:

Filtered_RawData_First:
Filtered_RawData_First = CALCULATETABLE( Raw_Data, FILTER( Raw_Data, Raw_Data[Date Extracted] = [SelectedDate_First] ) )
 
This table should filter data based on the selection in the First Date Filter. There is an active one-to-many relationship between First Date Filter and Raw_Data.
 

Filtered_RawData_Second:
Filtered_RawData_Second = CALCULATETABLE( Raw_Data, USERELATIONSHIP('Second Date Filter'[Date Extracted], Raw_Data[Date Extracted]) )

This table should filter data based on the selection in the Second Date Filter. There is an inactive one-to-many relationship between Second Date Filter and Raw_Data.

Issue:
Both Filtered_RawData_First and Filtered_RawData_Second are not filtering as expected. Instead of showing data for the selected date(s) in their respective slicers, they display data for all dates.

 

Any guidance on how to resolve this issue would be greatly appreciated!

Thanks in advance!

[v-sgandrathi]

Hi @Hashmeet,

Thank you for reaching out to the Microsoft Fabric Community forum.

The approach you’ve implemented is almost close to the expected result, but for the filtered tables to reflect the slicer selections correctly, we recommend creating measures to apply the selected values from your slicers. This ensures the dynamic behavior you are expecting.

For your convenience, I’ve attached the PBIX file with the implemented solution. Please review it and let us know if there are any additional adjustments needed.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Best Regards,
Sahasra.

[Hashmeet]

Thank you, Sahasra, that helps!

I have created SelectedDate_First and SelectedDate_Second, and they are reflecting the dates selected in the slicer. However, when I pull the Date Extracted column into a table visual from Filtered_RawData_First and Filtered_RawData_Second, it still shows all the dates from Raw_data instead of only the selected ones.

Let me know if there’s anything I might have missed or if additional adjustments are needed.

Regards,
Hashmeet

Filtering_Data: 

My Output:

[v-sgandrathi · Accepted solution]

Hi @Hashmeet,

You haven’t missed anything; the key adjustment is replacing the calculated columns with measures. The output you’re observing was happening because Calculated columns, like we created (Filtered_RawData_First and Filtered_RawData_Second), are static. They are evaluated at the time of data refresh and do not dynamically respond to slicers or other visual filters. This is why the table visual continues to display all dates from your Raw_Data.

To achieve the result, I suggest using measures( SelectedDate_First and SelectedDate_Second) instead of calculated columns. By using these measures in your table visual, the results will now adapt to any slicers applied.

If you need any further suggestions, please let us know. If this post clarifies your doubt, please give us Kudos and consider marking Accepting it as a solution to guide other members in finding it more easily.

Best Regards,

Sahasra.

[amitchandak]

@Hashmeet , Calculated table and Calculated column, can not take slicer value. They are calculated at load time and static after that.

Only measure can take slicer value. Cosider measure for you need

example

CALCULATE(Countrows( Raw_Data) , USERELATIONSHIP('Second Date Filter'[Date Extracted], Raw_Data[Date Extracted]) )