cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create table from boolean columns and ID of existing table

I have a table in Power BI that looks like the one below:

 

IDKeep1Keep2Keep3RedBlueStraightCurved
72396valuevaluevalue1001
39979valuevaluevalue1110
81829valuevaluevalue1101
10225valuevaluevalue0101
20289valuevaluevalue1010
94188valuevaluevalue0101
40660valuevaluevalue1010
42612valuevaluevalue1001

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Hi @jcody , I also agree with using reference and unpivot to get the new table. Please try the following steps:

 

  1. In Query Editor, right click on the original table query and click Reference, you will get a new query which references the original one.
  2. In the new query, select Red, Blue, Straight and Curved columns at the same time and unpivot cloumns.
  3. Filter out rows whose Value is 0.
  4. Remove the undesired columns. You can also remove the Value column here after the filtering.

 

v-jingzhang_0-1600075124639.jpeg

 

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.

View solution in original post

9 REPLIES 9
Highlighted
Impactful Individual
Impactful Individual

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.

// if this is a solution please mark as such. Kudos always appreciated.
Highlighted
Super User IV
Super User IV

@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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

@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...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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:

 

IDAttribute
72396Red
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?

Highlighted

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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.

Highlighted

@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!

Highlighted
Microsoft
Microsoft

Hi @jcody , I also agree with using reference and unpivot to get the new table. Please try the following steps:

 

  1. In Query Editor, right click on the original table query and click Reference, you will get a new query which references the original one.
  2. In the new query, select Red, Blue, Straight and Curved columns at the same time and unpivot cloumns.
  3. Filter out rows whose Value is 0.
  4. Remove the undesired columns. You can also remove the Value column here after the filtering.

 

v-jingzhang_0-1600075124639.jpeg

 

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.

View solution in original post

Highlighted

That worked great and the detailed instructions were very helpful. Thank you!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors