Forum Discussion
query data in several columns
What you are asking for is doable, but it would be simpler if you do the following:
1. Don't split your initial data into separate columns, and instead add a custom column using Text.Split([TextListColumn], ", ") to create a list of values
2. Click on the arrow at the top of the new column and choose "Expand to New Rows"
3. Either Filter that new column for "Strawberry", or load the data and use a DAX measure and/or slicers to get your desired counts/analysis.
Pat
Thanks mahoneypat , your solution worked 🙂
However now I have the following problem (derived from the expansion into new rows):
In column1:key I have several ids:
row1: 1
row2: 2
row3: 3
The column2 was the labels column described before (apple, strawberries, etc).
If I then expand the labels into new rows as you suggested before I get:
Colum1:key; Colum2:labels_expanded
row1: 1; apple
row2: 1; pear
row3: 2; apple
row4: 2; pear
row5: 3; strawberry
etc
If I then load the data and display the labels_expanded column, it will only show the last "label_expanded" value for the same key "id" value. For example:
Table visual:
headers: key; label_expanded; labels
row1: 1; pear; "apple,pear"
Would it be possible to display all the expanded labels for a certain id? So I could really use the slicer and say:
show me all the ids in which "apple" appear?
it should show me: key =1 and key = 2
with the current solution it shows me none as "apple" is not considered in the "labels_expanded" column.
Thanks again for your help.
jürgs
Not sure what you mean. If you make a table with just the Key column and choose a value from a slicer with your new unpivoted label column, you should see only the keys that contain that one. If you want a measure that shows you all the labels that contain the selected label, you can use a measure like this
AllLabels =
VAR label2 =
CALCULATETABLE ( DISTINCT ( Labels[Label] ), ALL ( Labels[Label] ) )
RETURN
CONCATENATEX ( label2, Labels[Label], ", " )
Pat