Filter DAX query by result of cube measure

[kopite833]

Hi, 

This script queries an OLAP cube and I must filter the result to only retrieve records where daily hours = 24 (e.g. [Roster Actual Sum Hours Nett] = 24).

E.g. of intended results are as follows...

Staff ID	Roster Week	Date	Hours	How query should handle this...
00001	WE 27-Jul	26/07/2021	8	Do not retrieve
00002	WE 27-Jul	25/07/2021	24	Must retrieve

How must I modify to achieve these results?

DEFINE
    VAR EndDate =
        IF (
            WEEKDAY ( NOW (), 1 ) = 4,
            NOW ()
                - MOD ( WEEKDAY ( NOW (), 1 ) + 3, 7 ) - 8,
            NOW ()
                - MOD ( WEEKDAY ( NOW (), 1 ) + 3, 7 ) - 1
        )
    VAR StartDate = EndDate - ( 7 * 52 ) + 1
EVALUATE
SELECTCOLUMNS(
    SUMMARIZECOLUMNS(
        Staff[StaffNumber],
        'Date'[RosterWeekLabel],
        'Date'[TransactionDate],
        'Pay Type'[Description],
        Site[SiteName],
        'Roster Type'[RosterType],
        FILTER(KEEPFILTERS(VALUES( 'Date'[TransactionDate] )), 'Date'[TransactionDate] >= StartDate),
        FILTER(KEEPFILTERS(VALUES( 'Date'[TransactionDate] )), 'Date'[TransactionDate] <= EndDate),
        "Roster Actual Sum Hours Nett", [Roster Actual Sum Hours Nett]
    ),
    "Staff ID", [StaffNumber],
    "Roster Week", [RosterWeekLabel],
    "Date", [TransactionDate],
    "Hours", [Roster Actual Sum Hours Nett]
)

[lbendlin · Accepted solution]

Apply the value filter ( == 24)  to your SELECTCOLUMNS "Hours" result?