Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table in Power BI that looks like the one below:
ID | Keep1 | Keep2 | Keep3 | Red | Blue | Straight | Curved |
72396 | value | value | value | 1 | 0 | 0 | 1 |
39979 | value | value | value | 1 | 1 | 1 | 0 |
81829 | value | value | value | 1 | 1 | 0 | 1 |
10225 | value | value | value | 0 | 1 | 0 | 1 |
20289 | value | value | value | 1 | 0 | 1 | 0 |
94188 | value | value | value | 0 | 1 | 0 | 1 |
40660 | value | value | value | 1 | 0 | 1 | 0 |
42612 | value | value | value | 1 | 0 | 0 | 1 |
I'm trying to create a slicer that would allow me to filter visuals on the "attribute" columns (Red, Blue, Straight, Curved). These column names are attributes that relate to the ID. I'm thinking I need a new table that has two columns - ID and Attribute. Each row in the new table would store each attribute that is "true" associated with each ID (one ID to many attributes). This new table would be joined to the original table by ID and would update / recalculate when I refresh the source data.
This seems like it should be simple with DAX, but given I am a novice, I haven't been able to solve for it.
Any guidance would be appreciated.
Solved! Go to Solution.
Hi @jcody , I also agree with using reference and unpivot to get the new table. Please try the following steps:
Additionally, documentation for Reference a query: https://docs.microsoft.com/en-us/power-bi/guidance/power-query-referenced-queries
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it more quickly.
Hi @jcody , I also agree with using reference and unpivot to get the new table. Please try the following steps:
Additionally, documentation for Reference a query: https://docs.microsoft.com/en-us/power-bi/guidance/power-query-referenced-queries
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it more quickly.
That worked great and the detailed instructions were very helpful. Thank you!
@jcody , There are two approaches to it. One is you Unpivot the data. Select the column in edit query and use the unpivot option
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
The second is like a measure slicer, Create a table of the column and create a measure that reacts to slicer values of this colum.
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
Thanks for your responses @samdthompson @Greg_Deckler @amitchandak .
I researched the unpivot option prior to posting here, and ruled it out for two reasons:
1. It is possible for multiple attributes to be 1 (TRUE) for any given ID. Unpivoting the attribute columns seems to combine all attributes into a single attribute column with the value being eithe 1 or 0.
2. Related to #1, the unpivoted columns remain in the same table as the original data, so I'm unable to filter by multiple attributes under #1.
So, ultimately, what I think I need is a separate table that contains the IDs and corresponding attributes that are 1 (TRUE) in the original data. It would look something like this:
ID | Attribute |
72396 | Red |
72396 | Curved |
39979 | Red |
39979 | Blue |
39979 | Straight |
Obviously this is fake data and 39979 wouldn't be both Red and Blue, but hopefully that illustrates the objective.
Is there a simple way to create this separate table without compromising the original data, but allowing me to filter based on these attributes?
@jcody - Well, you could right click your query, Reference and remove Keep columns, filter out 0's, do a remove duplicates and you should have your table.
However, I'm calling in @ImkeF @edhans and @HotChilli to see what their thoughts are on this.
I would like to know whether these columns go in mutually exclusive pairs.
Red/Blue Straight/Curved
Later on the OP says "Obviously this is fake data and 39979 wouldn't be both Red and Blue". so it does seem to imply this even though some of the data shows both Red and Blue.
So can you confirm and does this reflect the real data?
Once this is answered I'd also like to know what the OP would like to see in a final report. "A slicer" doesn't really make sense if we have mutually exclusive pairs.
@HotChilli these are not mutually exclusive pairs.
The actual attritbutes relate to heart conditions/diagnoses - chronic heart failure, angina, coronary bypass, etc.
So the final report(s) will allow me to present a variety of related datasets both in aggregate and also by one or more of those attributes.
Hope this answers your questions. Thanks again for your help!
@jcody I agree with @samdthompson There is a DAX Unpivot, but it's a last resort:
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256
this would be best achieved in power query.
In the query editor:
1.select the red, blue, stright and curved columns
2.click unpivot on the transform ribbon
this will then give you an attribute and values column.
// if this is a solution please mark as such. Kudos always appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |