Forum Discussion
Slicer: Data values in one table equal column names in another
Hi. I'm new to Power BI. I'm trying to create a slicer using the values in one table to toggle which columns display in another table. The values in table 1 are equal to the column name in table 2. I'm simplifying somewhat, but this is the basic idea:
| STATE |
| A |
| B |
| C |
| Name | A | B | C | TOTAL |
| John | 20.12 | 21.12 | 13.15 | 54.39 |
| Susan | 0 | 12.12 | 14.9 | 27.02 |
| Gary | 14.12 | 0.12 | 0 | 14.24 |
In the slicer, I'd like to select A and Cand have it update table 2 and some other visuals such as a donut chart based on Table 2 as well as recalculate the totals. Is there a way to do this? I tried adding a relationship between table 1 and table 2, but I'm unable to create a relationship between values in one table and the column names in another.
Thanks!
In your data, you want to make sure that you import it into your model as unpivoted. You can do this in the Query editor under the Tranform area of the ribbon. You want to get your data to be in a format such that each row has the columns Name, State, and Amount.
Once you have that imported you can create a slicer based on your State Column, which will display just a distinct list of your imported state values.
Next you can create a Matrix visual. Put Name into the rows. Put State into the columns and put Amount into value and select the aggregation of Sum.
2 Replies
In your data, you want to make sure that you import it into your model as unpivoted. You can do this in the Query editor under the Tranform area of the ribbon. You want to get your data to be in a format such that each row has the columns Name, State, and Amount.
Once you have that imported you can create a slicer based on your State Column, which will display just a distinct list of your imported state values.
Next you can create a Matrix visual. Put Name into the rows. Put State into the columns and put Amount into value and select the aggregation of Sum.
Anonymous Thank you so much! Unpivoting the individual columns into a single STATE column and using the Matrix visual did exactly what I needed.
