Forum Discussion
Text comparison (LIKE operator)
Hello Community
I've run into a slight problem whereby I'm looking to compare a text value against a list of values in a column, however these values are separated by a ";" for each row. eg.
Value to compare: "pillow"
Values in column [bedroom]: "pillow, bed, sofa,"
"pillow"
"bed, sofa"
My measure is as follows:
CALCULATE(DISTINCTCOUNT([name]) + 0, room[bedroom] = "pillow"
Based on the above example, I only have a count of 1 returned whereby I should have a count of 3.
Any thoughts on the above, I'm assumning it has something to do with the type of string comparison that is being used.
Any help would be much appreciated.
Thank you in advance.
you could use the FIND ( ) function to test if the text string is found
FIND (find_text, within_text, [start_num], [NotFoundValue])
Having said that, I think there is a very good chance that your data design is not optimal for Power Pivot. Can you share your table data structure here? I suspect there is a better way to pre-format your data before loading to be more efficient and effective.
3 Replies
you could use the FIND ( ) function to test if the text string is found
FIND (find_text, within_text, [start_num], [NotFoundValue])
Having said that, I think there is a very good chance that your data design is not optimal for Power Pivot. Can you share your table data structure here? I suspect there is a better way to pre-format your data before loading to be more efficient and effective.
Matt,
Thank you for your response and suggestion regarding the issue. I'm currently looking into alternate ways in which to store the data; unfortunately this has been designed by external developers and I don't have much say in the matter.
Failing that, will look at perhaps some functoins in which I can confirm whether the text value exists within the text string.
Power Query is explicit designed to help you clean and shape your data during load. You should check it out - it's easy to learn.
http://exceleratorbi.com.au/what-is-power-query/
the FIND function I mentioned will work otherwise
