Compare two text columns

[jessiew]

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

[v-yulgu-msft]

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]

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]

The problem is not solved yet.

[dsouzanev]

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]

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? 

[dsouzanev]

Hi,

I analyzed a bit more.

I saw that there are 'NaN' s in your data in the 'YTD Cumulative Qty %'  and 'LY Cumulative Qty %' measures which could have happened due to your division (maybe divide by 0). Do you know why these are coming or what valus should be there? Should it be 0? Because of these, your initial aggregations are not correct, which could be the problem. Maybe, if you resolve these, your problem will get corrected. I used, IFERROR - 'YTD Cumulative Qty% = IFERROR( [Cumulative YTD TotalQty]/CALCULATE([YTD TotalQty],ALLSELECTED(ViewInventoryAllLocationsWithSales[FMSI])),0)' with both your measures and blanks were converted to 10. So, wherever it showing both as 90, in reality, it is having a 90 and a 10. For example for an FMSI of 44, for the Family D00044U - 25, the values of 'YTD Cumulative Qty %'  and 'LY Cumulative Qty %' is 90 and 10 and for D00044U - 26 it is 10 and 90. For 44 both the values are different, so it is rightly "Yes" as the values are different.