Forum Discussion
Anonymous
6 years agoNot applicable
Need help with complex DAX question
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
1 Reply
- Greg_DecklerCommunity Champion
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]