Forum Discussion
Filter data based on unique value contained within column
Hello,
I have searched for an answer to no avail and am hoping that someone here may have some insight they can share.
I have two tables. Table A contains a large dataset of Campaigns with Revenue and ~30 dimensions. For simplicity:
| Campaign | Revenue | Dimension A |
| A | $10 | Value1, Value2, Value3 |
| B | $20 | Value1,Value3 |
| C | $30 | Value2 |
My goal is to use my 2nd table, Table B, to filter the data based on each dimesnion that I want to filter on. TableB contains for each dimension the unique values that might be used.
| Dimension A |
| Value1 |
| Value2 |
| Value3 |
If I were to filter on Value2, I would expect this result:
| Campaign | Revenue | Dimension A |
| A | $10 | Value1, Value2, Value3 |
| C | $30 | Value2 |
Due to the large number of columns, possible dimension values within each column, size of dataset, and the potential for the dataset to change over time (additional dimensions added or removed), I want to avoid splitting each column into a million more columns.
Appreciate any help that can be provided, thank you!!
Anonymous you can add a measure and then filter on this measure where measure value <> 0
Filter Dimension = VAR __selectedValue = SELECTEDVALUE ( Slicer[Slicer] ) RETURN CALCULATE ( COUNTROWS ( 'Table' ), CONTAINSSTRING ( 'Table'[Dimension A], __selectedValue ) )Would appreciate Kudos 🙂 if my solution helped.
10 Replies
Anonymous you can add a measure and then filter on this measure where measure value <> 0
Filter Dimension = VAR __selectedValue = SELECTEDVALUE ( Slicer[Slicer] ) RETURN CALCULATE ( COUNTROWS ( 'Table' ), CONTAINSSTRING ( 'Table'[Dimension A], __selectedValue ) )Would appreciate Kudos 🙂 if my solution helped.
Hi parry2k ,
The measure seems to give each row the same value (the number of rows in the entire data set).
When I use the dimension in Table B (Slicer[Slicer]), the result is no rows remain (due to the above calculation).Results on a smaller sample of the data. This is before selecting anything in the filter. Filter selection results in blank table as described above.
Seems like the containsstring function is not being evaulated at each row, but I am not too familiar with this function to understand a workaround.