Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
aileenkaz
Frequent Visitor

Slicer based on multiple columns

Hello,

I have a data with five columns (filter6, filter7, filter8, filter9, filter10). Each one is containing only values 1 or 2.

I am trying to create a hierarchical slice filter in which I will choose firstly the column and then value (1 or 2) and then show only those rows of data that have the selected value.

I tried to create separate table for the slicer but I cannot find a way how to create a relationship between this table and the data.

Probably I need some special table to work as a connection between these two. Or am I thinking completely wrong?

 

Please, can you help?

Thanks a lot!

hierarchy_slicer.png

1 ACCEPTED SOLUTION

Hi @aileenkaz ,

 

I think you can create three measures, and when the filter results of three slicers are met at the same time, the corresponding values will be displayed.

M_filter6 = 
var a = SELECTEDVALUE(DATA[Filter6])
return IF(MAX('Filter'[Value Code])=a,1,0)

M_filter7 = 
var a = SELECTEDVALUE(DATA[Filter7])
return IF(MAX('Filter'[Value Code])=a,1,0)

M_filter8 = 
var a = SELECTEDVALUE(DATA[Filter8])
return IF(MAX('Filter'[Value Code])=a,1,0)

vhenrykmstf_0-1630658014061.pngvhenrykmstf_1-1630658035650.png

 


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @aileenkaz ,

 

Your logic seems to be wrong. According to your description, you want to filter the rows in the DATA table by using specific columns in the Filer table as filters. The hierarchical structure constructed in this way is not desirable. Because the row filtered by the slicer based on this hierarchy does not exist.

just like:

vhenrykmstf_0-1630552961304.png

Should create a separate slicer, put the column into it. Then create a measure, and set the selected value to 1. Put the measure into the "Filters on this visual" in the DATA table, you can see the corresponding filtering results:

M_filter = 
var a = SELECTEDVALUE(DATA[Filter6])
return IF(MAX('Filter'[Value Code])=a,1,0)

vhenrykmstf_1-1630553139701.png

vhenrykmstf_2-1630553156252.png

 

Hope to help you. If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi, @v-henryk-mstf, thank you very much for your answer!

 

Yes, you are right.  I want to filter the rows in the DATA table by using specific columns in the Filer table as filters. But in the first step I want to choose which filter column I want to use, because only one filter should be used at a time (e.g. If I have selected value 1 for filter 6, I cannot select any other value in any other filter).

 

I am thinking about how to design it in such a way it does not take so much space. That's why I wanted to use hierarchical filter, but now I see why it would not work. Can you help me with this?

 

Another solution I was thinking about was to have:

- one slicer as a dropdown menu with filters to choose which column I want to filter DATA by

- another slicer to choose whether I want to filter by value 1 or 2

But this is probably too complicated, because the second slicer would have to dynamically change the column which should be filtered. Is it possible to show/hide whole slicer based on selection made in another slicer? That would simplify the problem.

 

Thanks for helping me.

Hi @aileenkaz ,

 

I think you can create three measures, and when the filter results of three slicers are met at the same time, the corresponding values will be displayed.

M_filter6 = 
var a = SELECTEDVALUE(DATA[Filter6])
return IF(MAX('Filter'[Value Code])=a,1,0)

M_filter7 = 
var a = SELECTEDVALUE(DATA[Filter7])
return IF(MAX('Filter'[Value Code])=a,1,0)

M_filter8 = 
var a = SELECTEDVALUE(DATA[Filter8])
return IF(MAX('Filter'[Value Code])=a,1,0)

vhenrykmstf_0-1630658014061.pngvhenrykmstf_1-1630658035650.png

 


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.