Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
Solved! Go to 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)
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 @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:
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)
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)
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |