Forum Discussion
JustinDoh1
Post Prodigy
Post ProdigyNeed 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: -----------------------------------------------------...
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.
burakkaragoz
Super User
Super UserHi 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