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.

       

       

      • jessiew's avatar
        jessiew
        Frequent Visitor
        8 years ago

        Hi dsouzanev,

         

        Thank you for taking the time making analysis. 

         

        If I create a measure for the "90% Flag Changed" with below switch statement:

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

        Then I got the correct result for each FMSI.

         

        I'm thinking of creating a Parameter table for the 90% Flag Changed with only Yes and No in the table, then link it to the measure so we can filter Yes or No.  

         

        Do you think it's doable?