Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
burhanml
New Member

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

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.

 

burhanml_0-1685049901114.png

 

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

burhanml_1-1685050069770.png
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:

UdemyQ2.1.PNG

 

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! 

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
devanshi
Helper V
Helper V

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

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors