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 III
Super User III

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

View solution in original post

1 REPLY 1
lbendlin
Super User III
Super User III

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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors