Forum Discussion
Create a slicer that filters data dynamically
I submitted a question yesterday for this with some helpful solutions, but I'm taking another jab to explain my situation better. The dataset includes Primary and Alternate (sub) records with attributes populated for all records. I'm looking for a solution that allows the user to see how the overall composition changes by way of visuals (pie chart/bar charts) if a single primary is replaced with an alternative (sub). The solution to my first question allows me to create a measure that populates a field within a table/matrix that achieves this. However, a measure can't be used as a slicer so here's my second go at this.
Base table
| ID |
Name | Key | Attribute/Color | If Primary - Has Sub | On Sale |
| 1 | Lulu | Primary | Red | Yes | Yes |
| 1 | Caitlin | Sub | Blue | No | No |
| 1 | Ashe | Sub | Yellow | No | Yes |
| 2 | Garen | Primary | Orange | No | No |
| 3 | Poppy | Primary | Black | No | No |
| 4 | Veigar | Primary | White | Yes | Yes |
| 4 | Lux | Sub | Red | No | Yes |
| 5 | Soraka | Primary | Blue | No | Yes |
| 6 | Poro | Primary | Yellow | Yes | No |
| 6 | Akshan | Sub | Orange | No | No |
| 6 | Mundo | Sub | Black | No | No |
Disconnected Table = calculatetable(summarize('Primary Table','Primary Table'[Name]),filter('Primary Table','Primary Table'[If Primary - Has Sub]="Yes"))
| Name |
| Lulu |
| Veigar |
| Poro |
Slicer1 = 'Disconnected Table'[Name]
Desired Slicer = Displays Names where ID matches that of Slicer1 selection e.g., If Slicer1 = "Lulu" then 'Lulu', 'Caitlin', 'Ashe' is available for selection
Desired Outcome: 1) populates selected record in Desired Slicer excluding all others records where ID matches that of Slicer1 selection , 2)populate all records where Key = 'Primary' where ID does not match Slicer1 selection and excludes all Sub where ID does not match Slicer1 selection
If Slicer1 = 'Lulu' and Desired Slicer='Caitlin', populates table to include only Caitlin where ID = 1, else if key = 'Primary' include, else exclude
Filtered Primary Table if Slicer1 = "Lulu" & Desired Slicer = 'Caitlin'
| ID | Name | Key | Attribute/Color | If Primary - Has Sub | On Sale |
| 1 | Caitlin | Sub | Blue | No | No |
| 2 | Garen | Primary | Orange | No | No |
| 3 | Poppy | Primary | Black | No | No |
| 4 | Veigar | Primary | White | Yes | Yes |
| 5 | Soraka | Primary | Blue | No | Yes |
| 6 | Poro | Primary | Yellow | Yes | No |
Filtered Primary Table if Slicer1 = "Lulu" & Desired Slicer = 'Lulu'
| ID | Name | Key | Attribute/Color | If Primary - Has Sub | On Sale |
| 1 | Lulu | Primary | Red | Yes | Yes |
| 2 | Garen | Primary | Orange | No | No |
| 3 | Poppy | Primary | Black | No | No |
| 4 | Veigar | Primary | White | Yes | Yes |
| 5 | Soraka | Primary | Blue | No | Yes |
| 6 | Poro | Primary | Yellow | Yes | No |
Filtered Primary Table if Slicer1 = "Lulu" & Desired Slicer = 'Ashe'
| ID | Name | Key | Attribute/Color | If Primary - Has Sub | On Sale |
| 1 | Ashe | Sub | Yellow | No | Yes |
| 2 | Garen | Primary | Orange | No | No |
| 3 | Poppy | Primary | Black | No | No |
| 4 | Veigar | Primary | White | Yes | Yes |
| 5 | Soraka | Primary | Blue | No | Yes |
| 6 | Poro | Primary | Yellow | Yes | No |
For anyone looking to achieve the same in Power BI, I used crossjoin to create a copy of the base table then created a custom column to serve as my slicer field.
3 Replies
DAX does not allow direct filtering between tables without a relationship, the logic says that you need to establish a connection.
One way to do this is by creating a column in your primary table that matches the values in the disconnected table.You can create a new column in your primary table that identifies if the record is related to one of the names in the disconnected table. This can be a simple boolean flag (True/False).
Then, ceate measures that dynamically filter the primary table based on the selection in Slicer1 and the Desired Slicer.
Use these measures in your visuals to reflect the changes based on the slicer selections.
I don't have your model so I am imagining something like this :
Connected Column = IF( 'Primary Table'[Name] IN VALUES('Disconnected Table'[Name]), TRUE(), FALSE() ) Filtered Data Measure = VAR selectedPrimary = SELECTEDVALUE('Disconnected Table'[Name]) VAR selectedSub = SELECTEDVALUE('Primary Table'[Name], "All") RETURN CALCULATE( COUNTROWS('Primary Table'), FILTER( 'Primary Table', IF( 'Primary Table'[Key] = "Primary" && 'Primary Table'[Connected Column] = TRUE(), 'Primary Table'[Name] = selectedPrimary || selectedSub = "All", 'Primary Table'[Key] = "Primary" ) ) )Hi otto-user101 ,
Please try this way:
First I use this DAX to create a new table to create Desired Slicer:Desired Slicer = SELECTCOLUMNS ( 'Primary Table', "ID", 'Primary Table'[ID], "Name", 'Primary Table'[Name] )
Then I create a measure by using this DAX in table Desired Slicer:Measure = VAR A = CALCULATE( MAX('Desired Slicer'[ID]), FILTER( ALLSELECTED('Desired Slicer'), 'Desired Slicer'[Name] = SELECTEDVALUE('Disconnected Table'[Name]) ) ) RETURN IF( ISFILTERED('Desired Slicer'), IF( SELECTEDVALUE('Desired Slicer'[ID]) = A, 1, 0 ), 0 )And the Desired Slicer is created, sorry I didn't change the name, you can change it yourself:
Then I create a measure by using DAX below in Primary Table:
Measure 2 = VAR A = CALCULATE( MAX('Primary Table'[ID]), FILTER( ALLSELECTED('Primary Table'), 'Primary Table'[Name] = SELECTEDVALUE('Disconnected Table'[Name]) ) ) RETURN IF( ISFILTERED('Desired Slicer'[Name]) && ISFILTERED('Disconnected Table'[Name]), IF( SELECTEDVALUE('Primary Table'[Name]) = SELECTEDVALUE('Desired Slicer'[Name]), 1, IF( SELECTEDVALUE('Primary Table'[ID]) <> A && SELECTEDVALUE('Primary Table'[Key]) = "Primary", 1, 0 ) ), 0 )The final result is shown below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.For anyone looking to achieve the same in Power BI, I used crossjoin to create a copy of the base table then created a custom column to serve as my slicer field.
