Forum Discussion

jessiew's avatar
jessiew
Frequent Visitor
8 years ago

Compare two text columns

I created below caculated column "90% Flag Changed" to compare two text column. When I tested it, I found most of the results are correct but some result even though the text in both columns are the same, it still give me "90% Flag Changed" Yes when I filter by Yes.

 

 

Appreciate if anyone can point me what is wrong in the logic.

 

Attached the pbix file in below shared URL:

https://drive.google.com/open?id=12WM1TGR7Qcl_q7-lUqYcp7W77KJo5p4_

 

The pbix file is around 3.6MB

 

 

 

Need Help

6 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi jessiew,

     

    As you referenced to two measures in calculated column "90% Flag Changed", measure values is often related to row context in visual, this might cause the wrong result in your scenario.

     

    Please create a "90% Flag Changed" measure rather than calculated column and add it to table visual.

    90% Flag Changed measure = IF(EXACT([YTD BI 90% Flag],[LY BI 90% Flag]),"No","Yes")

     

    Best regards,

    Yuliana Gu

    • jessiew's avatar
      jessiew
      Frequent Visitor
      8 years ago

      Thanks for the reply v-yulgu-msft

       

      But that won't achive the goal. Two reasons:

      1. Measure cannot be used as slicer or filter. Management wants to filter the 90% Flag Changed and easily to find the FMSI which changed. This is the main reason that I have to use a calculated column instead of a measure.

       

      2. IF statement cannot be used in measure. I can use the function SWITCH, but because of reason 1, I do not want to just add the comparison to the table visual, I want to be able to filter on it.