Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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_Deckler's avatar
    Greg_Deckler
    Community 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]