Forum Discussion
Relationship between multiple columns in one table and one column in a lookup table
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
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
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.
2 Replies
- mwegener
Most Valuable Professional
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
Community Champion
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