Using RELATED()

[procyon82]

Hi,

I only recently installed Power BI and I've been testing it for a few days. So far so good except that I am combining data from two different Google Analytics UA- tracking codes and I'm running into a problem. Here are my relationships, but I don't know if they are correct.

'Revenue per user per device' works (not correct though but just using as an example):

Revenue per user per device = RELATED(Ecommerce[Revenue]) / Traffic[Users]

How come 'revenue per user' which is Revenue per User = Ecommerce[Revenue] / RELATED(Traffic[Users]) does not work? It gives me the error: The column 'Traffic[Users]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Since the data is coming from different GA accounts I can't simply include 'Users' in my 'Ecommerce' query. I feel like I need be changing my relationships somehow.

[idanco · Accepted solution]

The relationship between ecommerce and Traffic is one -> many. 

Which means that for each row in ecommerce you can have more than one row in traffic.

Therefore, you must use RELATEDTABLE instead of related, And since this is going to return more than one row , You should wrap it in an aggregation function.

[procyon82]

Thanks! That's helpful. Can you elaborate on the aggregate function? I looked it up and none (https://msdn.microsoft.com/en-CA/library/ms173454.aspx) work for my purposes.

[deldersveld]

You linked to a T-SQL page. Try the DAX reference instead: https://msdn.microsoft.com/en-us/ee634396.aspx

Sum/SumX, etc. are aggregation functions.