Forum Discussion
Lookup multiple text values in multiple columns
Dear Power BI community,
I'm struggling with the following. I have an export with a particular column which shows various labels which have been assigned to a post. These labels are all in one column separated by comma. Here is an example of data. The column from export is how I load in the data in PowerBI, then with using queries, I seperate the column using delimiter comma and I get three new columns.
I need to create a stacked bar chart of 1 - labels vs 2 - labels. The advantage is dat all labels either start with 1 - or 2 - in front of it. Any combination of 1- and 2- labels is possible,
The problem is that 1 - labels could be in either the 1,2 or 3 column, same goes for 2 -. This depends on what label the agent assigns first to a post. Then there is also the issue that agents could add two times a 1 - label AND a 2 - label, which normally isnt allowed. In that case I want to select the FIRST 1 - label icm with the corresponding 2 - label.
I've created an relationship with another datafile that contains all 1- and 2- labels, but it doesnt work cause I have more than one columns.. I know I need to probably use DAX but don't have much experience with it..
Any help would be greatly appreciated!
Kind regards,
Maartje
Hi maartjedutchy,
>>Yeah I tried unpivot, but I have over 46 columns in this data source and 200.000 rows of data (therefore its difficult to share my PBIX).
I think you can spilt part of your data an share us as a sample to test, it will be help to analysis.
In addition, I think you not need to unpivot your original table, you can try to use search/find functions to lookup the related values and combine if statement to check with different conditions.
Regards,
Xiaoxin Sheng
4 Replies
maartjedutchy hi are able to share your pbix?
i think what you actually need to do is unpivot in power query all 3 columns to create a row for each one, that way you can easily remove duplicates and have an instance of each label and category.
HI vanessafvg,
Yeah I tried unpivot, but I have over 46 columns in this data source and 200.000 rows of data (therefore its difficult to share my PBIX).
Can I create a column where it looksup the text values starting with "1-" in multiple columns and IF yes, show that value in the new column?
maartjedutchy you could probably use the find or search function to see if it has it
see example here
http://community.powerbi.com/t5/Desktop/DAX-FIND-or-SEARCH-part-of-a-string-in-a-column/td-p/134518
that might be a bit performance hungry though, what is the issue around unpivoting? if you think about it you will be making your model more lean with less columns if you unpivot
