Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hey Community,
im working with PowerBI for several month now and im challenging by handling with multiple fact tables which are using the same conformed dimensions. I am familiar with the design and use of star scheme. Unfortunately, I can't find a post regarding drill-across multiple facttables or they always differ from my use case. What I have read so far is that you should merge queries, although this is not recommended according to the literature. i tried that and my merged facttables had multiply my values in the facttable.
I am sure that my problem is certainly not one at all and that I lack the necessary experience so far. For this reason, I would be very grateful if you could help me with your experience on the subject.
Back to my Problem:
I have 3 Dimension Tables
Furthermore i have several Fact Tables for this example lets work with 2 maybe more in future.
All FactTables are connected to each DimensionTable with an active (n:1) cardinality.
At this point everything looks good so far, but my visuals doesnt work at all, when i want to create a Table on the Report-Page in PowerBI. The table-visual contains the OrderNumber, CalenderDate, Revenue from the SalesOrderEntry FactTable. And when i include the Column from the SalesTurnover Facttable its only show me wrong aggregated sums (even when im changing the aggregate function in the visual fields) . (see below)
(DT) = Values from the Dimensional Table
(FT-SOE) = Values from the Fact Table SalesOrderEntry
(FT-ST) = Values from the Fact Table SalesTurnover
OrderNumber (FT-SOE) | CalenderDate (DT) | Revenue (FT-SOE) | Turnover (FT-ST) |
36001 | 01.01.2023 | 50.000 | 125.000 |
36002 | 04.01.2023 | 75.000 | 125.000 |
36003 | 12.01.2023 | 13.000 | 125.000 |
36004 | 21.01.2023 | 15.000 | 125.000 |
When i have fix this problem, than i would calculate the measures like a f.e. the Backlog.
I would like to show myself the Orders by revenue, turnover and the calculated backlog on one visual.
Please do not mix my request with the drill-through function of PowerBI, which I had to read here as well.
Best Regards to all and Thanks in advance for your help. 🙂
Solved! Go to Solution.
Most likely this is caused by picking fields from the "wrong" side of the relationship. Your filter fields must come from the dimension tables for the vast majority of scenarios. Alson note that the order in which fields appear in a visual has an impact on the join that is used.
Most likely this is caused by picking fields from the "wrong" side of the relationship. Your filter fields must come from the dimension tables for the vast majority of scenarios. Alson note that the order in which fields appear in a visual has an impact on the join that is used.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |