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
Frank
Frequent Visitor

Allocating Fact Table to Multiple Dimension Tables

I've searched high and low for this question but it seems most results are about multiple fact tables.

 

What I have: I have a basic star schema that comes from SQL Server.  A single fact table of transactions that has transaction facts, multiple explicit measures, a date to link to the Calendar table, and several outline ID's to link to the dimension tables.  The Dimension tables contain hierarchies if it matters, and the link is the leaf's ID.  I also use tabular editor for time functions like YTD, YoY, CAGR, etc. Everything works great.

 

What I want to add:  I want to add a dimension that I allocate to rather than a straight mapping.  I have a business case that says Customer A is 75% Market A and 25% Market B.  So far I have created an allocation table with that data (Customer, Market, Percent).  I can connect fact table to allocation table to dimension table which is how I think it should work?

 

My question is:  What is the best way to apply the allocation to all fact table measures?  I have a lot of explicit measures and I intend to have more allocated dimensions in the future so I would prefer to not have a complex Dax formula to maintain on each measure.  Many of my measures are not sums (avg, median, mode) so I don't want to join to the fact table and expand records on the SQL side.  Is there an elegant way of doing this or am I better off keeping a DAX formula template to copy and paste in and tweak for updates?

 

Thank you in advance

Frank

 

 

 

 

 

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

Hi, @Frank 

Have you found a possible way to get to the right destination to solve your problem?
If you have found the right direction, would you like to mark your own reply as a solution so that others can learn from it too?

Thanks very much!

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frank
Frequent Visitor

Hmmm...so I accidentally stumbled into a working solution.

 

I made a measure Sum of Share on the allocation table.  It is a many-to-many relationship so RELATED would not work, and I struggled to get the alternatives I found on here to work for my case.  I don't know why I couldn't figure out how to do this inside the fact table measure, and just gave this a shot when all else failed.

 

I changed my fact table measure Sum of Revenue = SUMX(FactTable, FactTable[Revenue] * [Sum of Share]) and let the relationships do most of the work.  I assume I can just keep multuplying new allocated dimensions.

 

This doesn't seem so bad just working with sums. I did have to add a little bit on the SQL side so the allocation table plugs missing and out of balance customers.

 

Am I painting myself into a corner?  Or will this hold up?

 

 

 

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.