Forum Discussion
Dynamic measure flag not working on every row
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:
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!
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.
3 Replies
- DataNinja777
Super User
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,
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?
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.
