cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kopite833
Frequent Visitor

Filter DAX query by result of cube measure

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 IDRoster WeekDateHoursHow query should handle this...
00001WE 27-Jul26/07/20218Do not retrieve
00002WE 27-Jul25/07/202124Must 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]
)

 

 

 

 

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!