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 all,
I have written the following DAX code for filtered table in DAXStudio:
EVALUATE
ADDCOLUMNS (
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
NATURALLEFTOUTERJOIN (
Calendar544W,
TimesheetFacts
),
Employees01[Person],
TimesheetFacts[CMHRWRKS],
TimesheetFacts[CMHRWRKI],
Calendar544W[Date],
Calendar544W[WeekdayNum],
Calendar544W[IsPublicHoliday]
),
"Schedule",
MAXX (
SUMMARIZE (
TimesheetFacts,
Employees01[Person],
TimesheetFacts[CMHRWRKS],
Calendar544W[WeekYearNum]
),
[CMHRWRKS]
)
),
FILTER (
Calendar544W,
Calendar544W[WeekYearNum] = 202001
),
FILTER (
Employees01,
Employees01[Person] = 537 || Employees01[Person] = 125
)
),
"# Gross hrs", CALCULATE ( SUM ( TimesheetFacts[KMHRCATSH] ) )
)
ORDER BY [Date], [Person]
The code gives the following output:
What I want is to have the column "Schedule" to show the value of column "CMHRWRKS" individually for both persons. So P90E for all lines belonging to person 125 and P80A for all lines belonging to person 537.
Can anyone tell me how this can be done?
Rg. Erwin
Sample source data would really help but maybe this:
EVALUATE
ADDCOLUMNS (
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
NATURALLEFTOUTERJOIN (
Calendar544W,
TimesheetFacts
),
Employees01[Person],
TimesheetFacts[CMHRWRKS],
TimesheetFacts[CMHRWRKI],
Calendar544W[Date],
Calendar544W[WeekdayNum],
Calendar544W[IsPublicHoliday]
),
"Schedule",
MAXX (
FILTER (
TimesheetFacts,
Employees01[Person] = EARLIER( Employees01[Person]
),
TimesheetFacts[CMHRWRKS]
)
),
FILTER (
Calendar544W,
Calendar544W[WeekYearNum] = 202001
),
FILTER (
Employees01,
Employees01[Person] = 537 || Employees01[Person] = 125
)
),
"# Gross hrs", CALCULATE ( SUM ( TimesheetFacts[KMHRCATSH] ) )
)
ORDER BY [Date], [Person]
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |