Forum Discussion
MikeHendriks
1 year agoHelper I
Complex DAX Statement based on existence in Dim; FTE based so sometimes sum and sometimes don't
As I work through a Power BI model, I’ve encountered a challenging DAX scenario that I’d love to get input on. The model consists of a fact table with straightforward relationships to dimensions such...
MikeHendriks
1 year agoHelper I
I think I've came close;
Staff Calculation =
VAR StaffData =
ADDCOLUMNS (
SUMMARIZE ( Fact, Fact[DateID], Fact[TimeID], Fact[CostCenterID], Fact[FloorID], Fact[OccupationID] ),
"@Staff",
VAR StaffValue =
CALCULATE (
MAX ( Occupation[StaffNeeded] ),
FILTER ( Occupation, Occupation[OccupationID] = Fact[OccupationID] )
)
RETURN IF ( ISBLANK(StaffValue), "Error", StaffValue )
)
VAR MaxPerDay =
ADDCOLUMNS (
SUMMARIZE ( StaffData, Fact[DateID], Fact[CostCenterID], Fact[FloorID] ),
"@MaxStaff", MAXX ( FILTER( StaffData, Fact[DateID] = EARLIER(Fact[DateID]) && Fact[CostCenterID] = EARLIER(Fact[CostCenterID]) && Fact[FloorID] = EARLIER(Fact[FloorID]) ), [@Staff] )
)
VAR FinalCalculation =
SUMX ( MaxPerDay, [@MaxStaff] )
RETURN FinalCalculation
This outputs this;
Almost what I need I think, but I only expected a textmessage on the Type A 20250102 on the morning, because the MAX is indeed 3, but there is also a value which can't be found on the dim so it should be a textmessage instead.
Any suggestions here?