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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RedPeppery
Frequent Visitor

Slicer is not acting as it should in visual, bridge relation between fact, date, products, kpi

I have the following problem that I don't know how to treat:

Data scheme is this:

RedPeppery_4-1680932749096.png

Kpi table has this type of information 

RedPeppery_8-1680933539806.png

Product table has only unique products(bridge)

RedPeppery_0-1680933856383.png

 

 

Fact Table is 

RedPeppery_7-1680933508704.png

 

 

Calendar table(DatesKey) that is in relation[date] with Facttable - daily sales

Calendar table (DatesKey) is in relation[quarter] with Quarter table  that is in relation[quarter] with the KPI table (productsNordenTable)

KPITable(productsNordenTable) is in relation[product] with Products table that is in relation[product] with FactTable

 

I have 2 slicers in visual:

RedPeppery_6-1680933050786.png

Why are 2 products selected in facts table even if in product table is only one? The slicer is working selecting only the products from the fact table that are for KPI1 but it should select only the ones in Q2 and not in Q1 as well, so it shold select only P2 and not P1 as well

 

Thank you

 

4 REPLIES 4
P_d2023
Regular Visitor

firstly i would simplify this by having the quarter and month table as part of the datekey table.

 

im not sure what the KPI table is doing but it seems to be another Fact Table connecting this through product is what is causing the filter to be confused.only one fact table should be connected to the date table. you would have to break thye connection between product and (kpi or fact table). to connect kpi and fact table you would need a common field between the 2 this can usally be achieved by combining columns or grouping the table to make one of the tables act as a dim

tamerj1
Super User
Super User

Hi @RedPeppery 
This is becuase your data model is ambiguous. The filter from DatesKeyMonths is populated to the fact table through two different paths. The reuslts would be unexpected and very difficult to explain. This applies also to DatesKey and DatesKeyQuarter tables.

1.png

Products is a dim table and should not be filtered, rather you can follow below schema and handle the calculations with DAX.

2.png

so instead of using just a simple countrows(facttable) I have to use something like calculate(countrows(facttable), treatas(values(kpi[product],factable[product]), in order to calculate only and use the slicer that KPI apply?

@RedPeppery 

It depends on your business logic. But yes this is one way of doing it

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors