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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ppc_raltandi
Frequent Visitor

Showing Filter Relevant Values between Aggregated Table and Fact Table

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:

 

ppc_raltandi_1-1692296048578.png

 

 

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:

 

ppc_raltandi_2-1692296381350.png

 

 

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:

 

ppc_raltandi_3-1692296493315.png

 

Currently, if I use the Dimension Table as a slicer, it will show ALL  5 values that are in my dimension table, like so:

 

ppc_raltandi_4-1692296597890.png

 

Any help appreciated!

1 ACCEPTED SOLUTION
ppc_raltandi
Frequent Visitor

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.


https://community.fabric.microsoft.com/t5/Desktop/How-to-create-slicer-for-multiple-fact-tables-show...

View solution in original post

1 REPLY 1
ppc_raltandi
Frequent Visitor

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.


https://community.fabric.microsoft.com/t5/Desktop/How-to-create-slicer-for-multiple-fact-tables-show...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.