Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Guys,
I'm quite new to power bi. I have just one lookup table and multiple fact tables and their relationships are all one to many. I want to create a slicer using category column in the lookup table to filter one specific table based on either category or old category columns. Also this slicer is filtering other visuals as a normal filter.
For example if you select golf, this one specific table is going to show category = golf or old category = golf.
So I'm thinking that I can create a measure counting rows based on the criteria above and get 1 if true for each row and filter that measure = 1
I created this measure:
P.S. I have tried creating a diconnected slicer table including all categories and it worked, but in the ideal situation, I only need one slicer only from lookup table instead of two.
Here is the sample file.
https://drive.google.com/file/d/15gVjklMerU-AgayrmMqezptByh3tQq51/view?usp=sharing
Try this solution. You can put the measure in a visual filter.
https://community.powerbi.com/t5/Desktop/Once-Slicer-for-multiple-columns/m-p/430979#M198545
Proud to be a Super User!
Hi, thanks for your reply. I took a look at the solution and seems like you have to created a disconnected slicer table with no relationships to any table. So it's impossible to just use the one column from the lookup table as a slicer to control two columns in the fact table while still have a one to many relationship between them?
Here's an alternative that allows you to keep the slicer connected to the data model. Create a calculated table that is a summary of the fact table (summarize by the columns displayed in the visual):
SummaryTable = SUMMARIZE ( MA_Up_Downgrade, MA_Up_Downgrade[Category Desc], MA_Up_Downgrade[Old Category], MA_Up_Downgrade[Type] )
This table has no relationships. Add columns from this table to the visual. Create measure:
Filter Visual =
VAR vCategorySelected =
VALUES ( MA_Lookup[Category Desc] )
VAR vResult =
CALCULATE (
COUNTROWS ( SummaryTable ),
FILTER (
SummaryTable,
SummaryTable[Category Desc]
IN vCategorySelected
|| SummaryTable[Old Category] IN vCategorySelected
)
)
RETURN
vResult
Filter the visual where this measure = 1.
-------------------------------------------------------------
The link below is to a similar post. The difference is that no additional fact table columns are in the visual. Since a slicer often controls multiple visuals, it's preferable to keep the slicer connected to the data model and create a disconnected summary table for special cases.
Proud to be a Super User!
User | Count |
---|---|
77 | |
77 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
65 |