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
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? 

  • dsouzanev's avatar
    dsouzanev
    Regular Visitor
    8 years ago

    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.