Create table from boolean columns and ID of existing table

[jcody]

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.

[v-jingzhang · Accepted solution]

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.

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.

[jcody]

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

[amitchandak]

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

[jcody]

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?

[Greg_Deckler]

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

[HotChilli]

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.

[jcody]

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

[Greg_Deckler]

@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

[samdthompson]

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.