Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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.
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.
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,
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?
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |