Forum Discussion

Coryanthony's avatar
Coryanthony
Helper III
2 years ago

Help with Calculated Column, Switch TRUE. PLEASE

Hello,

I have what i thought was a basic calculated column, but having issues that i can't explain or figure out. Please please help.

 
Threshold = SWITCH(TRUE(),
[Total Cash Claim] > 3000 && 'Cash Claims'[Employee Custom 11 - Name] = "Partner", "Partner",
[Total Cash Claim] > 1000 && 'Cash Claims'[Employee Custom 11 - Name] <> "Partner", "Employee", "NO")
 
Column description - If [Total Cash Claim] is > 3000 && [Employee Custom 11 - Name] = "Partner", then i want text to be "Partner"
                                   If [Total Cash Claim] is > 1000 && [Employee Custom 11 - Name] does not = "Partner", then i want text to                                         be "Employee", IF not, then "NO"
 
WHen i place this column in a table, it is not displaying the correct result, additionally, it is changing the [Total Cash Claim] results.
 
Please see Table A, i have the theshold column there but it changed the amount and also reporting "NO" rather than "Partner"
 
I cannot seem to figure this out. Please help. Thank you

 

 
 
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Coryanthony , The below will work only with [Total Cash Claim] is not a measure.

     

    Threshold = SWITCH(TRUE(),
    [Total Cash Claim] > 3000 && 'Cash Claims'[Employee Custom 11 - Name] = "Partner", "Partner",
    [Total Cash Claim] > 1000 && 'Cash Claims'[Employee Custom 11 - Name] <> "Partner", "Employee", "NO")

     

    If [Total Cash Claim] is measured then you need a measure and dynamic Segmentation

     measure

     

    Threshold = SWITCH(TRUE(),
    [Total Cash Claim] > 3000 && max('Cash Claims'[Employee Custom 11 - Name]) = "Partner", "Partner",
    [Total Cash Claim] > 1000 &&max('Cash Claims'[Employee Custom 11 - Name])  <> "Partner", "Employee", "NO")

     

     

     

    example

    Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

    • Coryanthony's avatar
      Coryanthony
      Helper III
      2 years ago

      Hi amitchandak 

      I did try eliminating the measure and while that did not change the amount, the logic was not accurate. I was not getting any "NO". If under the amounts i was still getting TRUE. Please see example below. 

      I also want to count the amount of "Partner" and "Employee"; therefore, i was hoping to get this in a column.

       

      Threshold = SWITCH(TRUE(),
      SUM('Cash Claims'[Claimed Amount (rpt)]) > 3000 && 'Cash Claims'[Employee Custom 11 - Name] = "Partner", "Partner",
      SUM('Cash Claims'[Claimed Amount (rpt)]) > 1000 && 'Cash Claims'[Employee Custom 11 - Name] <> "Partner", "Employee", "NO")
       
      based on logic, highlighted should be "NO"
       

       

       

      I appreciate you so much. Thank you.

  • Coryanthony's avatar
    Coryanthony
    Helper III
    2 years ago

    amitchandak 

    I just watched you vidro and it was awesome. I should be able to make it with a measure.

    But if i wanted the calculated column, what would be the best approach?