Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have two tables:
1. Connections (information on connected person, time etc.)
2. Message Classification (Unique messages sent)
Now I want to see all connections, that don't have a message yet.
So far so good:
Tables can be linked via URL. It is a one-to-many relationship (1 side: Connections, many side: Messages).
Due to some other relationships I can only use an inactive relationship and make use of the USERELATIONSHIP Function.
This is my measure:
Persons w no Mess = CALCULATE(
DISTINCTCOUNTNOBLANK(Connections[URL])
, USERELATIONSHIP(Connections[URL], 'Messages Classification'[Contacted Person URL])
)
But I always receive too many values for the messaged person. I get all possible combinations in my output table (like the full scalar value) and I don't know why, since there is a valid connection between these two tables...
This is the output:
I only want to retrieve one single row per Connection. And if there is no message for a connection, than show blank as message column.
Hope you get what I mean and can help 😊
Due to some other relationships I can only use an inactive relationship and make use of the USERELATIONSHIP Function.
That's not what USERELATIONSHIP is for. Its main purpose is to switch connections between two tables. Let's say from the Calendar Date to either the Order Date or the Ship Date. Yes, you can use it to "switch on/off" a single relationship but ONLY if switching that on will not result in a circular reference/path ambiguity. Which is most likely what happens in your scenario, leading to the cartesian result.
User | Count |
---|---|
84 | |
71 | |
71 | |
67 | |
55 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |