Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm trying to add a slicer/filter on my report page that shows only relevant values that appear on the FACT table. This is as easy as just setting the Cross Filter direction between the Dimension and Fact table as "Both". However, I had to add an Aggregated Table to show the overall performance of a measure versus the user's performance. This resulted in an aggregated table that has four dimensions in common with the FACT table. Below is the relationship as it currently is:
The table on the left is my FACT table, the tables in the middle are my dimension tables, the table on the right is my aggregated table. The aggregated table aggregates the data by LOBKey, ProgramKey, Date, and InsuranceCategoryKey.
The visuals on the report work as I expect them to, however, I want to add slicers for LOBKey (Line of Business), ProgramKey (Program), and InsuranceCategoryKey (Insurance) to a report page or even as a Filter on the Filters pane. My issue is that if I add the slicer or filter straight from the dimension table, it will show all values on the dimension table while I only need relevant values. In the past I simply changed the Cross Filter Direction between the fact and dimension tables to "Both", but now I have the aggregated table and PowerBI only lets me set it to "Both" for one table at a time, it won't let me set it to Both for the relationships between all the dimension and fact table, like below:
I need the slicers on my report page to look like this, since there are only two relevant values for the particular data point I'm looking at:
Currently, if I use the Dimension Table as a slicer, it will show ALL 5 values that are in my dimension table, like so:
Any help appreciated!
Solved! Go to Solution.
I will mark this as solved based on this topic I found which worked for my case:
I created a measure for each dimension table and then added the measure to the filter of each slicer.
I will mark this as solved based on this topic I found which worked for my case:
I created a measure for each dimension table and then added the measure to the filter of each slicer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
97 | |
85 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |