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

The 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.

Reply
Bonkers
Frequent Visitor

Drill-Across (not Drill-Through) Sales

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

  • Calender
  • Customer
  • Sales Representant
  • Business Line

Furthermore i have several Fact Tables for this example lets work with 2 maybe more in future.

  • SalesOrderEntry (OrderNumber, OrderDate, customer_id, salesrep_id, businessline_id,revenue, costs)
  • SalesTurnover(OrderNumber, TurnoverDate,customer_id,salesrep_id,businessline_id,turnover)

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)
3600101.01.202350.000125.000
3600204.01.202375.000125.000
3600312.01.202313.000125.000
3600421.01.202315.000125.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. 🙂

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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