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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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