Stairway to SSAS Tabular Level 8: Multiple Fact Tables and Perspectives

Question

Post reply

Stairway to SSAS Tabular Level 8: Multiple Fact Tables and Perspectives

Thomas LeBlanc

SSCrazy Eights

Points: 8085
More actions
October 9, 2019 at 12:00 am

#3680365

Comments posted to this topic are about the item Stairway to SSAS Tabular Level 8: Multiple Fact Tables and Perspectives
Thomas LeBlanc, MVP Data Platform Consultant

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Deepak Gupta Valued Member Points: 59 More actions · Answer 1

Hi Thomas,

Thanks for this great series.

One question on this topic though. If I wish to add a measure that shows Sales minus Transactions or something like that which joins 2 Fact tables, then may I know what's the best way to do that? I know that the user can simply add a column in Excel. But I'd like to include it as part of the SSAS model so that it's easier for the end-user to simply include it and maybe even use it with Power BI connected to the same.

Thoughts?

Deepak Gupta

pietlinden SSC Guru Points: 63482 More actions · Answer 2

Create a measure for Total Sales, and another measure for Total Transactions, and then a third where you subtract the two. (I like building simple measures and then combining them to make more complex ones... DAX is hard enough as is!)

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 3

Hi pietlinden,

Thanks for reading the article and you are right, DAX is hard.

Thomas

Thomas LeBlanc, MVP Data Platform Consultant

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 4

Deepak

Yes, you can take 2 measures from different tables and perform aggregations on them. Measures are unique throughout the model, so on any table, you can reference the measure in another measure like subtracting [Total Sales] from [Total Transactions]

Thomas

Thomas LeBlanc, MVP Data Platform Consultant

Deepak Gupta Valued Member Points: 59 More actions · Answer 5

Hi Thomas and pietlinden,

Thanks for the tip! I didn't know we could do that. It worked for me in 1 model but not the other (there might be something else going on with the relationships). But at least I know what to do.

Thanks again.

Deepak.