topic Re: Need help with multi IF statements in DAX Commands and Tips

Need help with multi IF statements

JustinDoh1 — Fri, 06 Jun 2025 22:01:32 GMT

I have attached my PBIX file here.

I am trying to create a column ("Difference to 5-Star condition3") with outputs based on the logic below:

------------------------------------------------------------------------------------------------------------

If ShortStayScore and LongStayScore are different,

Subtract its total (ShortStayScore + LongStayScore) from 1456 --> (meaning: 1456 – ((ShortStayScore + LongStayScore)),

If the value becomes negative, put blank.

If ShortStayScore and LongStayScore are same (meaning ShortStayScore = ‘Not Reported’),

Subtract LongStayScore from 736 --> (meaning: 736 – (LongStayScore)),

If the value becomes negative, put blank.

------------------------------------------------------------------------------------------------------------

Here is examples:

I only illustrated 6 examples here:

Thanks for help.

Re: Need help with multi IF statements

burakkaragoz — Tue, 01 Jul 2025 09:08:49 GMT

Hi JustinDoh1 ,

Great explanation and thanks for providing clear logic and examples! You can achieve this in Power BI using a DAX calculated column with nested IF statements to handle both scenarios.

Here’s the DAX you can use:

Difference to 5-Star condition3 = 
VAR ShortStay = [ShortStayScore]
VAR LongStay = [LongStayScore]
VAR BothScoresReported = 
    AND(
        ShortStay <> "Not Reported",
        LongStay <> "Not Reported"
    )
VAR BothScoresSame = ShortStay = LongStay

// If both scores are reported and different, use the first logic
RETURN
IF(
    BothScoresReported && NOT BothScoresSame,
    VAR Diff = 1456 - (ShortStay + LongStay)
    RETURN IF(Diff < 0, BLANK(), Diff),
    // If both scores are the same (or ShortStayScore = 'Not Reported'), use the second logic
    IF(
        LongStay <> "Not Reported",
        VAR Diff2 = 736 - LongStay
        RETURN IF(Diff2 < 0, BLANK(), Diff2),
        BLANK()
    )
)

A few extra tips:

Make sure that “Not Reported” is treated as a text value (not a number). If your data is numeric with blanks for “Not Reported”, use ISBLANK([ShortStayScore]) or ISBLANK([LongStayScore]) instead.
Adjust the column names as needed to match your data model exactly.
If you want to display the word "BLANK" instead of a blank value, you can replace BLANK() with "BLANK" in the IF statements.

Let me know if you need help adjusting this logic for your specific dataset, or if you want to handle more scenarios!

Happy to help further if needed!
translation and formatting supported by AI

Re: Need help with multi IF statements

JustinDoh1 — Mon, 09 Jun 2025 01:09:50 GMT

burakkaragoz Thank you so much for your help.

I tried creating a column, and got this bottom message:

Re: Need help with multi IF statements

v-ssriganesh — Mon, 09 Jun 2025 11:49:28 GMT

Hi JustinDoh1,
Thank you for reaching out to the Microsoft fabric community forum.

I have reproduced your scenario in Power BI Desktop and successfully achieved the expected output as per your requirements.

For your reference, I’m attaching the .pbix file containing the solution. You can download it and review the setup to apply it to your report.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

Re: Need help with multi IF statements

danextian — Mon, 09 Jun 2025 22:47:17 GMT

Hi JustinDoh1

Please try this:

Difference to 5-Star condition3 = VAR _ShortStayScore = tblSLTC_FSPI[ShortStayScore] VAR _LongStayScore = tblSLTC_FSPI[LongStayScore] VAR _ShortLongScore = _ShortStayScore + _LongStayScore VAR _Diff1 = 1456 - _ShortLongScore VAR _Diff2 = 736 - _LongStayScore RETURN SWITCH ( TRUE (), _ShortStayScore <> _LongStayScore, MAX ( _Diff1, BLANK () ), --blank is bigger than negative tblSLTC_FSPI[ShortStayLogic] = "Not Reported", MAX ( _Diff2, BLANK () ) --blank is bigger than negative )

burakkaragoz Let us please not rely too much on AI and validate the solutions we provide. ShortStayScore and LongStayScore are both numbers so comparing them with text in variable BothScoresReported will return an error.