Need Urgent Help with on DAX Query (SWITCH/TRUE)

[burhanml]

Hi there,

I am currently preparing a Balance Sheet based on some dummy data (data is clean).

Here are some screenshots for you to follow where I am currently at in the process:

1. This is the Balance Sheet (Matrix Design):

The reason why the data has the same values is because I created a measure called "BS Values" with the following formula:

BS Values =

CALCULATE( SUM('Balance Sheet Data'[Balance Sheet Values]),

    TREATAS(VALUES(Dates[Year]),'Balance Sheet Data'[Year]))

However, this is not the issue at hand. See screenshot and continue reading to Point #2.

2. This is the SWITCH/ TRUE formula I used based on the first formula:

Formula written: 

B/S Values =
VAR CurrentItem = SELECTEDVALUE('Balance Sheet Template'[Balance Sheet Normalized])

RETURN
SWITCH(TRUE(),
CurrentItem = "Total current assets" , [Current Assets],
CurrentItem = "Total fixed assets", [Fixed Assets],
CurrentItem = "Total Other Assets", [Other Assets],
CurrentItem = "Total Assets" = [Total Assets],
CurrentItem = "Total current liabilities" = [Current Liabilities],
CurrentItem = "Total long-term liabilities" = [Long-Term Liabilities],
CurrentItem = "Total owner's equity" = [Owner's Equity],
CurrentItem = "Total Liabilities and Owner's Equity", [Liabilities And Owners Equity],
CurrentItem = "Debt Ratio (Total Liabilities / Total Assets)", FORMAT(DIVIDE([Total Liabilities],[Total Assets],0),"0.00"),
CurrentItem = "Current Ratio (Current Assets / Current Liabilities)", FORMAT(DIVIDE([Current Assets],[Current Liabilities],0),"0.00"),
CurrentItem = "Working Capital (Current Assets - Current Liabilities)", FORMAT([Current Assets] - [Current Liabilities],"0"),
CurrentItem = "Assets-to-Equity Ratio (Total Assets / Owner's Equity)", FORMAT(DIVIDE([Total Assets],[Owner's Equity],0),"0.00"),
CurrentItem = "Debt-to-Equity Ratio (Total Liabilities / Owner's Equity)",FORMAT(DIVIDE([Total Liabilities],[Owner's Equity],0),"0.00"),
CALCULATE([BS Values], FILTER('Balance Sheet Data','Balance Sheet Data'[Sub Category]= CurrentItem)))

3. HOWEVER, when I input the following formula into my matrix design, I am getting the following error:

I'm not really sure where I'm going wrong. In the Transform data i.e. PowerQuery, The Year column in the Dates table and the Balance Sheet [Dates] table is both set at ABC. 

Please let me know where I'm going wrong. I need to be able to figure this out before I can move on to the Cash Flow Statement! 

[devanshi]

Your dax query is right only remove format function  wherever you have written in query 

[Anonymous · Accepted solution]

Hi @burhanml,

I fix the typo and remove these FORMAT function usages from your expressions, you can try to use the following measure formula if it work on your side:

B/S Values =
VAR CurrentItem =
    SELECTEDVALUE ( 'Balance Sheet Template'[Balance Sheet Normalized] )
RETURN
    SWITCH (
        CurrentItem,
        "Total current assets", [Current Assets],
        "Total fixed assets", [Fixed Assets],
        "Total Other Assets", [Other Assets],
        "Total Assets", [Total Assets],
        "Total current liabilities", [Current Liabilities],
        "Total long-term liabilities", [Long-Term Liabilities],
        "Total owner's equity", [Owner's Equity],
        "Total Liabilities and Owner's Equity", [Liabilities And Owners Equity],
        "Debt Ratio (Total Liabilities / Total Assets)", DIVIDE ( [Total Liabilities], [Total Assets], 0 ),
        "Current Ratio (Current Assets / Current Liabilities)", DIVIDE ( [Current Assets], [Current Liabilities], 0 ),
        "Working Capital (Current Assets - Current Liabilities)", [Current Assets] - [Current Liabilities],
        "Assets-to-Equity Ratio (Total Assets / Owner's Equity)", DIVIDE ( [Total Assets], [Owner's Equity], 0 ),
        "Debt-to-Equity Ratio (Total Liabilities / Owner's Equity)", DIVIDE ( [Total Liabilities], [Owner's Equity], 0 ),
        CALCULATE (
            [BS Values],
            FILTER (
                'Balance Sheet Data',
                'Balance Sheet Data'[Sub Category] = CurrentItem
            )
        )
    )

Notice: FORMAT function will convert the result to text type, current power bi not support one calculate field return multiple data types.

FORMAT function (DAX) - DAX | Microsoft Learn
I'd like to suggest you to do these formatting operations on the field string format.

Use custom format strings in Power BI Desktop - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@burhanml Looks like lines 9, 10, 11, and 12 have an = where a , is needed.