Complex DAX Statement based on existence in Dim; FTE based so sometimes sum and sometimes don't

[MikeHendriks]

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 as CostCenter, Floor, Date, and Time. However, the real complexity comes from the OccupationID column and its corresponding dimension.

The dimension table Occupation contains a column, StaffNeeded, which specifies the number of staff required for the related record in the Fact tables. In some cases, there may not be a corresponding entry in the dimension table, and in those cases, I need the measure to return a text value such as "Error" instead of a blank or zero.

Key Requirements:

1. Daily Aggregation: If 3 staff are needed in the morning and 4 in the afternoon, the daily requirement should be 4 (the max value) and not 7 (the total).
2. Summation Across Multiple CostCenters and Dates: While the daily max per daypart should be taken, the final aggregation across multiple cost centers and days should sum the values accordingly. So 2 days with 3 staff needed on day 1 and 4 staff needed on day 2 is 7 (the total) and not 4 (the max value)
3. Different Aggregation Logic for CostCenter Types:
   • Type A CostCenters: Aggregation should be done on FloorID.
   • Type B CostCenters: Aggregation should be done on CostCenterID (which means multiple floors should be grouped together).
   • Different behaviour as described is already prepared in the data in Facts table.
4. Totals with errors: When "error" is outputted somewhere all above listed totals will be "error" as well

Expected Outcome

For example, if on two different days across five cost centers, four staff are required per time segment, the calculation should be:

2 days × 5 cost centers × 4 staff = 40 staff.

For the attached example file I expect this outcomes:

I would greatly appreciate any advice, insights, or full DAX implementations from the community. Has anyone tackled a similar problem? How would you approach this?

Example PBIX can be found here; https://easyupload.io/f9av8f

[MikeHendriks]

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?

[MikeHendriks]

Sounds logic @AilleryO. Was also what I was thinking off, but also have to find a trick to pickup the value from the dim IF the value of the fact is in the dim as well.

[AilleryO]

Hi @MikeHendriks ,

As far as I understand, your requirement is to use different calculation on different levels in your matrix or visual table.

If my diagnosis is correct, you should consider using functions like (HASONEFILTER, ISFILTERED, ISCROSSFILTERED, ISINSCOPE...) to identify at which level of your table you are.

In you case it means that you will test if a COSTCENTER, a FLOOR etc has a filter or not, or a single value (with HASONEVALUE). According to that yuo can adjust your calculation.

For instance in the following formula I'm testing if I'm on the level of an invoice, if yes then I want a BLANK, otherwise it means that I'm at the client level in my visual so I want the count of invoice.

Number of invoices (with test) =
IF( HASONEFILTER( SALES[ID_Invoice] ) //If one invoice is filtered

 , BLANK() ,                            //Return a  blank instead of 1

 [Measure count of invoice] )  //Return the measure that counts the invoices

or another example :

Total Sales OR Average invoice amount of client = //Dispaly the sum or the average
IF(
    HASONEVALUE( SALES[ID_Invoice] ) , //If there is only one invoice, we want the total sales of the invoice

    FORMAT( [Total Sales] ,"#,### EUR" ) ,  //So we display and format the measure Total Sales (a simple SUMX( SALES, Price * Qty ) )

    IF( NOT ISBLANK([Average amount of invoices of client]) , "Average:" & FORMAT( [Average amount of invoices of client] ,"#,### EUR" , BLANK() ) ) 

)//If you have more than one invoice (you are on the client level in the table) so display Average amount, except if its blank

Hope it helps