Forum Discussion

cstanley1543's avatar
cstanley1543
Frequent Visitor
2 years ago

Calculated Column with IF Then from multiple columns

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.  
Need Help

3 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    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's avatar
      cstanley1543
      Frequent Visitor
      2 years ago

      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_'s avatar
        Wilson_
        Memorable Member
        2 years ago

        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?)