Select column based on slicer

[msl088]

Hello,

I have a situation where I want to filter an entire table based on a slicer selection. I have created 7 different boolean columns that represent whether or not that row fulfills a series of conditions. Now I want to use a slicer to select which column I want to filter the table through. Is there any way to do that?

Thanks in advance for your help! 

[amitchandak]

@msl088 , Not very clear.

In case you need column/measure slicer

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Or you have unpivoted data

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[msl088]

Here's an example of what I want to do. I have this data table.

GKEY	WEIGHT	SIZE	1	2	3	4	5	6	7
1234	4000	40	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
6541	3500	45	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
3120	12000	20	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
6584	4200	22	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
2310	6300	42	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5168	4000	20	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE
9841	6000	40	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE

And I have a stacked bar chart that I need to change through a slicer like this.

I think it's clearer now.

[parry2k]

@msl088 as @nvprasad recommended, unpivot your table. Follow these steps

- transform data
- select GKey, Weight, Size columns
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

Now you can use attribute as a slicer, or what you renamed it to,

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[msl088]

@parry2k unfortunately when I do that I get an error saying that I cannot have multiple values of "gkey" since it is on the one side of a many-to-one relationship and is used as a primary key of the table.

Is there any other way to do that?

[Ashish_Mathur]

Hi,

Using the Query Editor, create a unique list of all Gkey's and related the unpivoted table and the other table to this new table with the unique list of all Gkeys.

[msl088]

The problem is that the Gkey is also the primary key of the table, so I can't have multiple rows with the same Gkey, and unpivoting does that.

[parry2k]

@msl088 so this is a larger modelling challenge, as @Ashish_Mathur  suggested, create a table with unique gkey column and then use that table to link to this unpvitoed table and also with other tables you have in the model.

Data modelling is key part of any BI report and this is exactly is what we need it here.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[parry2k]

@msl088 not sure which table you are setting the relationship with and why you need that. 

[msl088]

I have a separate table which I cannot change and it is related to this table through the gkey column on a many to one relationship. 

[nvprasad]

Hi,

In the power query editor do the unpivot transformation to get all 7 columns into the one column and their responses in the next column.

This way you can slice based on 7 columns.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad