Calculated Column with IF Then from multiple columns

[cstanley1543]

I have a table with a list of records that were associated with multiple people.  Each person can record what they did to the record (Disposition1 - 5).  I'm trying to create a calculated column to store a single value that is not equal to a certain value.  For instance, 

FinalDisp = IF(MV_Incident[Disposition1]="BU", [Disposition2], IF([Disposition2] = "BU", [Disposition3], If([Disposition3] = "BU",[Disposition4],[Disposition5])))

Even though the formula says it's correct, it never gets past using Disposition2.  

[Wilson_]

Hi cstanley1543,

First of all, I would re-write your calculated column as:

FinalDisp = 
SWITCH (
    TRUE(),
    MV_Incident[Disposition1] = "BU", MV_Incident[Disposition2],
    MV_Incident[Disposition2] = "BU", MV_Incident[Disposition3],
    MV_Incident[Disposition3] = "BU", MV_Incident[Disposition4],
    MV_Incident[Disposition5]
)

1. Always put the table name in front if you are referring to a column. (I am assuming those should be column references and not measure references.)
2. SWITCH( TRUE(), ... ) is a much more readible way to do a nested IF statement in Power BI

If that doesn't correct the issue, maybe confirm with your data that there are any records where the Disposition1 or Disposition2 columns are not "BU". It could be that it's legitimately not reaching to the later conditions.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[cstanley1543]

Hi Wilson,

Thank you for the break down on the SWITCH function.  Unfortunately, it still doesn't get past the first statement and still only uses Disposition2.  

[Wilson_]

cstanley1543,

Like I said, if that doesn't correct the issue, maybe confirm with your data that there are any records where the Disposition1 or Disposition2 columns are not "BU". It could be that it's legitimately not reaching to the later conditions.

If you're still having issues, any chance you could share an anonymized pbix file?

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)