Combining summary data from two tables

[bramadan]

Hello, 

I am relative novice with power BI and was hoping you may help me with a task that seems simple but has been eluding me thus far.

I have two different long tables, is a list of registered clients and the other is a list of service events. Each client and each event are categorized into classes (which could be thought of as regions). Those clases are the same between client and event tables. Tables also have date columns indicating the time of service and the time during which client was active - but we can ignore those columns for a moment.

In the standard report view it is very easy to get summary information out of each table individually. For example, I can get the number of clients in each category from one table and the number of events in each category from the other. 

What I would like to do is create the report which combines the two and lets me compute using both. For example, I would like to report on how many events per customer happened in each category.

I can not simply link the tables on category, because in both tables each category occurs many times and is therefore not a valid join. 

One way I thought off is to create a whole new table in which 'category' will be a column with one unique entry on each row and than construct calculated columns for that table by filtering my original two tables based on their category columns matching. This works but seems like an extremely inelegant solution. 

Does anyone have better idea?

[AlbertoFerrari · Accepted solution]

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

[bramadan]

This sounds great. Will try it out immediately - and will check out your book too. 

Thanks a lot !