topic Re: Dynamic measure flag not working on every row in DAX Commands and Tips

Dynamic measure flag not working on every row

Anonymous — Tue, 24 Sep 2024 13:25:08 GMT

I'm attempting to use DAX code that will flag survey years within a window of time. The window is equal to a selected year and 2 years before. The selected year will change based on the ID that is selected so I'm trying to use a measure over a column.

My current DAX attempt is:

Window Flag =

VAR SurvYear = selectedvalue('My Hospital Summary'[Survey Year])

VAR flag =

calculate(

countrows('Def Peer Group Summary'),filter('Def Peer Group Summary', 'Def Peer Group Summary'[Survey Year] <= SurvYear && 'Def Peer Group Summary'[Survey Year] >= SurvYear-2))

Return flag

My current result is:

It's almost doing exactly what I need but for some reason it is not counting the rows of all the IDs. Ideally in the above screenshot there should be a 1 next to every year that is 2021-2023.

Any help would be great appreciated!

Re: Dynamic measure flag not working on every row

DataNinja777 — Tue, 24 Sep 2024 15:05:41 GMT

Hi Anonymous

The key here is using the ALL('Def Peer Group Summary') to remove the filter on any other columns that might be limiting the results to a specific ID. This should ensure that all years between 2021 and 2023 are flagged with a 1, regardless of the selected ID.

Window Flag = VAR SurvYear = SELECTEDVALUE('My Hospital Summary'[Survey Year]) RETURN CALCULATE( IF( 'Def Peer Group Summary'[Survey Year] <= SurvYear && 'Def Peer Group Summary'[Survey Year] >= SurvYear - 2, 1, -- Return 1 for True 0 -- Return 0 for False ), ALL('Def Peer Group Summary') -- Ignores filter context on the table for ID )

Best regards,

Re: Dynamic measure flag not working on every row

Anonymous — Tue, 24 Sep 2024 15:36:43 GMT

Hi DataNinja777

Thank you for telling me about the all function. However, when I try to use apply this, I get an error about 'Def Peer Group Summary'[Survey Year]. The error says it is not able to determine a single value. The Survey Year is not currently in a date table or anything like that to be unique values. Does it need to be to make use of this code?

Re: Dynamic measure flag not working on every row

Anonymous — Mon, 30 Sep 2024 07:55:39 GMT

Hi Anonymous ,

Based on the description, check if any filters are applied. Using the following DAX formula.

Window Flag = VAR SurvYear = SELECTEDVALUE('My Hospital Summary'[Survey Year]) var _defyear = SELECTEDVALUE('Def Peer Group Summary'[Survey Year]) VAR flag = calculate( countrows('Def Peer Group Summary'),filter('Def Peer Group Summary', 'Def Peer Group Summary'[Survey Year] <= SurvYear && 'Def Peer Group Summary'[Survey Year] >= SurvYear-2)) Return flag // RETURN // IF( // _defyear <= SurvYear && // _defyear >= SurvYear - 2, // 1, -- Return 1 for True // 0 -- Return 0 for False // )

Best Regards,

Wisdom Wu

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