Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the following problem that I don't know how to treat:
Data scheme is this:
Kpi table has this type of information
Product table has only unique products(bridge)
Fact Table is
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:
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
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
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.
Products is a dim table and should not be filtered, rather you can follow below schema and handle the calculations with DAX.
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?
It depends on your business logic. But yes this is one way of doing it
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |