DAX seems to only apply SWITCH function to column subtotals

[sstalcup]

Hello all,

I am trying to model an income statement in Power BI.

At a high level, I have 3 tables that are of interest:

- Accounting Dimensions (basically a list of accounts)

Accounting_Dimension_ID	Accounting_Dimension_1	Accounting_Dimension_1_Name	Account_Grouping	PowerBI_Sort
1012	100	Sales - External	Sales	1
1028	null	null	Gross Profit	3

- Divisions

Division_ID	Company	Division	Division_Name	Division_Description	Division_Grouping	Power_BI_Sort
7	100	100	Division 100	Division 100	Division 100	1
6	100	160	Division 160	Division 160	Division 160	2

- Ledger

Ledger_Line_ID	Accounting_Date	Recorded_Amount	Accounting_Dimension_ID	Division_ID
61957	2024-01-01	-100.25	1012	6
62485	2024-01-01	12.56	1002	6

The model is as follows:

I'm trying to use a DAX function to make calculations that summarize certain accounts in different ways. In this example, I'm trying to calculate gross profit based on the Sales amount - COGS. The DAX expression is as follows:

Test GP = 
SWITCH (
    TRUE (),
    MAX('Accounting Dimensions'[PowerBI_Sort]) = 3,
        CALCULATE (
            [Ledger $ Amount],
            FILTER (
                ALL('Accounting Dimensions'),
                'Accounting Dimensions'[PowerBI_Sort] IN {1, 2}
            )
        ),
    [Ledger $ Amount]
)

This doesn't quite work as expected, the calulation works for the column subtotal in the matrix visual, but does not work with the appropriate column context. You can see an example here:

Looking for some help how I might ensure the DAX calculation works for each column. 

[lbendlin]

Your problem is quite a bit deeper than that.  Power BI likes to have a proper data model with dimension tables controlling fact tables in a Star schema.  You don't have that yet.  Before trying to troubleshoot the DAX you will want to fix your data model.

[sstalcup]

Do you have suggestions about how I might improve the data model?

I'm confident that the model is already in a star schema with the ledger (fact) having many to one relationships with the accounting dimensions (dim) and divisions (dim). I've greatly simplified the model for this post, but I'm certainly open to suggestions.

[lbendlin · Accepted solution]

It's not in a star schema - you have bidirectional links between all tables. Eliminate them if you can, make them single direction from the dimension to the fact.

[sstalcup]

All I had to do was change the filter direction and it worked! Really appreciate the suggestion. Thanks!