Change a measure so it can be filtered?

[Anonymous]

I have the following measure that gives me the average business days in a column, for only year 2021

The issue is that I cannot use any filters in my slicer on it

2020 Average SLA =

CALCULATE (

AVERAGE ( 'Pivoted Audit Log'[Working Days] ),

FILTER(

'Pivoted Audit Log',

'Pivoted Audit Log'[Latest TM Action Date].[Year] < 2021

)

)

Question: Is there a way to create this measure, but so that filters have an effect on this measure? 

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your request, I made the following attempts to get the average value of each year based on the year, and then put the year field in the slicer to filter. The expected results are as follows:

M = 
CALCULATE (
    AVERAGE ( 'Pivoted Audit Log'[Value] ),
    FILTER (
        ALL ( 'Pivoted Audit Log' ),
        'Pivoted Audit Log'[Working Days].[Year]
            = MAX ( 'Pivoted Audit Log'[Working Days].[Year] )
    )
)

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[amitchandak]

@Anonymous , You can filter that using a visual level filter

or create an independent table and create a new measure using that. - Segmentation or Binning

Refer: https://www.youtube.com/watch?v=CuczXPj0N-k

or

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization