Forum Discussion
Solved
Unable to filter for multiple values in column
I am working in Power BI. I have Premium Capacity.
Source Data is Excel.
The source column has multiple values separated by semicolon.
This shows my scenario from left to right:
I am not able to return accurate results from the slicer if mulitple values are selected because the filter context only looks for a single value.
I have tried:
1] Create a separate table for the slicer default values, create relationship to source data table with both one to one and many to one cardinality
> the issue is the graphs from Population only show data where the column has a single value. It won't show the data where the column had multiple values or shows them all combined in their own bar.
2] create separate columns for Population so each value has its own column
> the issue is the filter only looks at the first column. It can caused missed data if the value was in columns 2,3,4,5
There should be a way to deal with columns that have multiple values separated by semicolon, show only unique values in the slicer, but filter context needs to pass and return rows where any one or more slicer filter values is found in the one column in any order.
There may be a better solution but this one works for me:
Create a new table to serve as a Cross Reference with Right-click 'Reference' in Data view.
In that table, remove all columns except the ones one with multiple values and the ID column.
Split each column that has multiple values by delimiter [ comma ] into rows.
Create a new table for each column that has multiple values with Right-click 'Reference'. This creates the values for the slicer for a given column.
In the slicer values table, right-click and remove duplicates.
At this point there should be three tables:
1] the original source data table
2] the cross reference table that has the multiple values split into rows and has an ID column
3] the slicer table that has only the unique values from any one column that had multiple values, no other columns.
In Relationships,
1] Connect slicer table to cross reference table, many to one, one way, on the column name (that had multiple values).
2] Connect cross reference to source table, many to one, two way, on ID.
For the visualization, The slicer gets its values / choices from the slicer table. The graphs to show the actual data gets its values from the source table. End result is the slicer shows unique values but selection of one or more values returns all matching hits in the source table as it should.
I also changed source data to be a SharePoint list instead of Excel, however, the solution should work with an Excel data source the same way.
There may be a better solution but this one works for me:
Create a new table to serve as a Cross Reference with Right-click 'Reference' in Data view.
In that table, remove all columns except the ones one with multiple values and the ID column.
Split each column that has multiple values by delimiter [ comma ] into rows.
Create a new table for each column that has multiple values with Right-click 'Reference'. This creates the values for the slicer for a given column.
In the slicer values table, right-click and remove duplicates.
At this point there should be three tables:
1] the original source data table
2] the cross reference table that has the multiple values split into rows and has an ID column
3] the slicer table that has only the unique values from any one column that had multiple values, no other columns.
In Relationships,
1] Connect slicer table to cross reference table, many to one, one way, on the column name (that had multiple values).
2] Connect cross reference to source table, many to one, two way, on ID.
For the visualization, The slicer gets its values / choices from the slicer table. The graphs to show the actual data gets its values from the source table. End result is the slicer shows unique values but selection of one or more values returns all matching hits in the source table as it should.
I also changed source data to be a SharePoint list instead of Excel, however, the solution should work with an Excel data source the same way.
