Measure as Page Level Filter

[ksan048]

Hi all,

I have  a number of measures that I have created which are dependent on each other and I would like the last one to be sued as a page level filter.

First measure :  Calculates the LOS score for the last month 

LOS Last Month Score = CALCULATE(AVERAGE(Sheet1[LOS Score]),dateadd(Sheet1[Date],-1,month))

Second measure:  Calculates the LOS score for the  month 

LOS Month Score = CALCULATE(AVERAGE(Sheet1[LOS Score]),dateadd(Sheet1[Date].[date],1,month))

Third measure: Compares the 2 scores

LOS Worse than Last Month = if([LOS Last Month Score]<[LOS Month Score],"Y","N")

I then want to filter based on the last measure.

How can this be done?

Thanks

[v-yuta-msft]

Hi ksan048,

To achieve your requirement, please use dax formula below:

LOS Worse than Last Month =
VAR LOS_Last_Month_Score =
    CALCULATE ( AVERAGE ( Sheet1[LOS Score] ), DATEADD ( Sheet1[Date], -1, MONTH ) )
VAR LOS_Month_Score =
    CALCULATE (
        AVERAGE ( Sheet1[LOS Score] ),
        DATEADD ( Sheet1[Date].[date], 1, MONTH )
    )
RETURN
    IF ( LOS_Last_Month_Score < LOS_Month_Score, "Y", "N" )

Regards,

Jimmy Tao

[ksan048]

@v-yuta-msft

Thanks for replying. I gave it a go and it seemed to only return me 'N'.

I stuck all the measures in a table including the solution you  provided which I have named the column as 'LOS worse' as shown below. (I have omitted the first column of IDs from the image).

I'm new to Power BI and need a bit of guidance! 

Thanks a lot!

[wildmight2017]

Were you able to figure out a solution? I'm trying to achieve something similar - and getting the same issue of repeating value. My suspicion is that you need to apply certain ALL() statements for it to work properly given granularity. 

[ksan048]

@wildmight2017 I hadn't manage to figure it out and ended up using Tableau instead as it accepted shape files that are of linestring types. I didn't need a workaround at all.......