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

    • jessiew's avatar
      jessiew
      Frequent Visitor
      8 years ago

      The problem is not solved yet.

      • dsouzanev's avatar
        dsouzanev
        Regular Visitor
        8 years ago

        The problem is not with your logic, but because you are using measures that actually aggregate over columns. I analyzed your pbix and found that wherever there is a same value and it is a Yes, there are two entries with either one of the percentage blank (See the screenshot, an example is with FMSI = 44). So you may want to restrategize. Hope this helps.