Dynamic measure flag not working on every row

[c_riley525]

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!

[v-jiewu-msft · Accepted solution]

Hi @c_riley525 ,

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.

[DataNinja777]

Hi @c_riley525 

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,

[c_riley525]

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?