Forum Discussion
Combining summary data from two tables
Using calculated columns it would be awful, I agree, but you can turn your solution into an elegant one, as the original idea is just fine.
First, build the table with unique values (you can use DAX to perform this), then build two relationships from your tables into this new one. Once this is done, the new table will filter both customers and services, and your model will work perfectly fine.
Technically speaking, you have two fact tables and you need an intermediate dimension to filter them. I don't think I ever blogged about it, but it is one of the first techinques of my last data modeling book which, given your question, I suggest reading. Sorry for the ads, it was not intentional, but I just believe you might like that book content.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Using calculated columns it would be awful, I agree, but you can turn your solution into an elegant one, as the original idea is just fine.
First, build the table with unique values (you can use DAX to perform this), then build two relationships from your tables into this new one. Once this is done, the new table will filter both customers and services, and your model will work perfectly fine.
Technically speaking, you have two fact tables and you need an intermediate dimension to filter them. I don't think I ever blogged about it, but it is one of the first techinques of my last data modeling book which, given your question, I suggest reading. Sorry for the ads, it was not intentional, but I just believe you might like that book content.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
This sounds great. Will try it out immediately - and will check out your book too.
Thanks a lot !