The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Gyus,
I think I'm not the first with such a question but I could not find a suitable post!
I have a table with all users incl. information which is similar to this:
UserName | User ID |
Maike | 1 |
Susi | 2 |
Now I have a table where I have to reference the user more times:
Object | CalledBy | CanceldBy | StartedBy | TestedBy |
A | 1 | 1 | 2 | 2 |
B | 2 | 1 | 2 | 1 |
However, I can only create a reference once, because the second time I can only create an inactive reference to user table.
Is there now a "smart" solution for this problem or do I have to create a separate user table for each column where I need a user.
Thank you very much!
Greetings
Solved! Go to Solution.
You have two options. The first is to create the multiple relationships and activate them in your measures. Lets say the active relation is between Table1[User ID] and Table2[CalledBy].
You have a measure that counts the Called By like this.
Called By = COUNTROWS ( Table2 )
Then we can also use that to count the other versions by activating an inactive link using USERELATIONSHIP
Canceled By = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[CanceledBy] ) )
StartedBy = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[StartedBy] ) )
Tested By = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[TestedBy] ) )
Each of the measures identifies the inactive relationship to turn on to do the calculation.
The other option would be to unpivot your columns so table 2 ends up looking like this:
Object | Type | User ID |
A | CalledBy | 1 |
A | CanceldBy | 1 |
A | StartedBy | 2 |
A | TestedBy | 2 |
B | CalledBy | 2 |
B | CanceldBy | 1 |
B | StartedBy | 2 |
B | TestedBy | 1 |
You have two options. The first is to create the multiple relationships and activate them in your measures. Lets say the active relation is between Table1[User ID] and Table2[CalledBy].
You have a measure that counts the Called By like this.
Called By = COUNTROWS ( Table2 )
Then we can also use that to count the other versions by activating an inactive link using USERELATIONSHIP
Canceled By = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[CanceledBy] ) )
StartedBy = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[StartedBy] ) )
Tested By = CALCULATE ( [Called By], USERELATIONSHIP ( Table1[User ID], Table2[TestedBy] ) )
Each of the measures identifies the inactive relationship to turn on to do the calculation.
The other option would be to unpivot your columns so table 2 ends up looking like this:
Object | Type | User ID |
A | CalledBy | 1 |
A | CanceldBy | 1 |
A | StartedBy | 2 |
A | TestedBy | 2 |
B | CalledBy | 2 |
B | CanceldBy | 1 |
B | StartedBy | 2 |
B | TestedBy | 1 |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
83 | |
64 | |
58 |
User | Count |
---|---|
246 | |
124 | |
115 | |
79 | |
78 |