Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
1 year ago
Solved

Need help with multi IF statements

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.

 

OR

 

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.

 

"DAX " "DAX help" 'Need help'
  • danextian's avatar
    danextian
    1 year ago

    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. 

4 Replies

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    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

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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.

    • danextian's avatar
      danextian
      Super User
      1 year ago

      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.