Table Relationships


#1

Hi Sam,

i have a clients table and a transactions table.

The client can also have partners attached to it. However both client and partner can have transactions.

Just trying to understand what is the best way to relate the 2 tables to calculate the client and partner revenue together?

Would it be advisable not to link them together and instead use COUNTROWS based on the specific columns? i.e-calculate client partner separately with variables?

image


#2

My feeling is you should break out your client and partners into separate table.

One - Client
One - Partner

Then you should have two fact table.

One - Transaction table
One - (Current Client table)

Then you give yourself the opportunity to filter by either separately OR individually if you want to.

This way all your relationships will be active as well.

Give it a try. I’m interested how this goes, and it works for the calcs you need.

Chrs


#3

Thanks Sam.

i’ll keep you posted shortly on how i go with the results


#4

Hi Sam,

Some feedback around this query, I was able to unpivot the partners and added a indicator next to them to identify the partners in the Query Editor.

The DAX measures came naturally after that!!

Thanks


#5

Great
Sam