Relationship between multiple columns in one table and one column in a lookup table

[Anonymous]

Hi,

I'm very new to PowerBI so this question may be too easy, but I hope someone can help me with it anyway:

I have one table with 3 columns: CaseNumber, OwnerID and CreatedByID.
I also have another table with 2 columns: UserID and FullName.

Now, I want to present two graphs: one showing the number of cases by owner and another showing the number of cases by creator. However, since the OwnerID and CreatedByID only show computer-generated numbers, I need to find the names of the owners/creators in the second table. To do this, I've created a relationship between OwnerID and UserID, as well as between CreatedByID and UserID.

Logically, when I try to create my bar chart based on this structure, PowerBI always uses the OwnerID-UserID relationship when I add FullName as a field. This makes it impossible for me to display the number of cases by creator.

Obviously I'm not using the relationships right, what can I do to fix this?

Thanks in advance!

Nina

[az38 · Accepted solution]

hi @Anonymous 

for example, you can create a calculated column in your 'Table1':

Column = 
LOOKUPVALUE('Table2'[FullName];'Table2'[UserId];'Table1'[CreatedByID])

do not hesitate to give a kudo to useful posts and mark solutions as solution

[mwegener · Accepted solution]

Hi @Anonymous ,

use LOOKUPVALUE.

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

For more information look at this.

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[az38 · Accepted solution]

hi @Anonymous 

for example, you can create a calculated column in your 'Table1':

Column = 
LOOKUPVALUE('Table2'[FullName];'Table2'[UserId];'Table1'[CreatedByID])

do not hesitate to give a kudo to useful posts and mark solutions as solution