Make a measure that filters a fact table respect the slicer visual

[dimitrisbut]

I have the following DAX measure:

TimeFrame Count =

CALCULATE(

    COUNTROWS('Actual'),

    FILTER('Actual',

        Actual[ID] = EARLIER('Actual'[ID])

        && 'Actual'[TimeFrame] = EARLIER('Actual'[TimeFrame])

    ),

    'Dates'[Month Name]= "February",

    'Dates'[Fiscal Year]="2023-2024"

)

I would like to remove the last two filters, 

'Dates'[Month Name]= "February",

    'Dates'[Fiscal Year]="2023-2024",

and make the measure work more dynamically based on month and year slicers. However, if I do remove them, The measure somehow ignores the month and year slicers I have on the page and uses the entire fact table for the calculations. How can I solve this and why does this happen?

[123abc · Accepted solution]

Please try this:

TimeFrame Count =
CALCULATE(
COUNTROWS('Actual'),
FILTER(
ALL('Dates'),
'Dates'[Month Name] IN VALUES('Dates'[Month Name])
&& 'Dates'[Fiscal Year] IN VALUES('Dates'[Fiscal Year])
)
)

This measure removes the hardcoded filters and instead uses the VALUES function to return the current filter context for the ‘Dates’[Month Name] and ‘Dates’[Fiscal Year] columns, which is set by the slicers. The ALL function is used to remove any existing filters on the ‘Dates’ table before applying the new filter context.

The reason your measure was ignoring the slicers when you removed the hardcoded filters is likely because the EARLIER function was creating a row context that was not interacting with the filter context from the slicers. By using VALUES, you ensure that the measure respects the current filter context, which includes the slicer selections.

Remember to replace ‘Actual’ and ‘Dates’ with the actual names of your tables if they are different. Also, ensure that your slicers are correctly configured to interact with the ‘Dates’ table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[v-huijiey-msft · Accepted solution]

Hi @dimitrisbut ,

Thanks for the reply from @123abc , please allow me to provide another insight:

Please try ALLSELECTED function:

TimeFrame Count =
CALCULATE(
     COUNTROWS('Actual'),
     FILTER(
         'Actual',
         'Actual'[ID] = EARLIER('Actual'[ID])
         && 'Actual'[TimeFrame] = EARLIER('Actual'[TimeFrame])
     ),
     ALLSELECTED('Dates')
)

The ALLSELECTED function returns all values in all rows or columns in a table, ignoring any filters that may have been applied inside the query, but retaining filters from outside.

For more information about the ALLSELECTED function, please see:

ALLSELECTED function (DAX) - DAX | Microsoft Learn

ALLSELECTED - DAX Guide

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[123abc · Accepted solution]

Please try this:

TimeFrame Count =
CALCULATE(
COUNTROWS('Actual'),
FILTER(
ALL('Dates'),
'Dates'[Month Name] IN VALUES('Dates'[Month Name])
&& 'Dates'[Fiscal Year] IN VALUES('Dates'[Fiscal Year])
)
)

This measure removes the hardcoded filters and instead uses the VALUES function to return the current filter context for the ‘Dates’[Month Name] and ‘Dates’[Fiscal Year] columns, which is set by the slicers. The ALL function is used to remove any existing filters on the ‘Dates’ table before applying the new filter context.

The reason your measure was ignoring the slicers when you removed the hardcoded filters is likely because the EARLIER function was creating a row context that was not interacting with the filter context from the slicers. By using VALUES, you ensure that the measure respects the current filter context, which includes the slicer selections.

Remember to replace ‘Actual’ and ‘Dates’ with the actual names of your tables if they are different. Also, ensure that your slicers are correctly configured to interact with the ‘Dates’ table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.