Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Solved! Go to Solution.
Apply the value filter ( == 24) to your SELECTCOLUMNS "Hours" result?
Apply the value filter ( == 24) to your SELECTCOLUMNS "Hours" result?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |