Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have a dataset similar to the one shown below.
I need to create a measure that counts how many reciprocal relationships we have, so if a particular user sends a message to the receiver, and then the receiver sends back the message, that's 1 reciprocal relationship.
In the screenshot you can see there are a total of three.
Thank you!
Solved! Go to Solution.
Hi @Sab ,
That likely means your sender id is not unique and that it matches many purchase id. Try this instead to return only the first value:
TransmitEcho =
VAR _Receivers = VALUES('Table'[Receiver])
RETURN
IF(
[Sender] in _Receivers,
VAR _ID = CALCULATE(MIN('Table'[ID]), FILTER('Table', 'Table'[Sender] = EARLIER('Table'[Receiver])), ALL('Table'[Receiver]))
VAR _Key =
SWITCH( TRUE(),
ISBLANK(_ID), BLANK(),
[ID] > _ID, COMBINEVALUES("^",_ID, [ID]),
COMBINEVALUES("^", [ID], _ID))
RETURN
_Key
)
If this doesn't work, if you can send some larger sample data for me to work with and I can test.
Hi,
Create this calculated column formula
Is this combination available elsewhere?=CALCULATE(COUNTROWS(Data),FILTER(data,Data[SenderID]=EARLIER(Data[ReceiverID])&&Data[ReceiverID]=EARLIER(Data[SenderID])))
Write this measure and drag it to a card visual
Measure = calculate(countrows(Data),Data[Is this combination available elsewhere?]=1)/2
This will work only as long as a Send and Receiver ID pair appears twice only.
Thanks for all your help!
Hi,
Create this calculated column formula
Is this combination available elsewhere?=CALCULATE(COUNTROWS(Data),FILTER(data,Data[SenderID]=EARLIER(Data[ReceiverID])&&Data[ReceiverID]=EARLIER(Data[SenderID])))
Write this measure and drag it to a card visual
Measure = calculate(countrows(Data),Data[Is this combination available elsewhere?]=1)/2
This will work only as long as a Send and Receiver ID pair appears twice only.
Hello and thank you for your reply @hnguy71 .
Strangely I am getting the error, 'A table of multiple values was supplied where a single value was expected.'
Hi @Sab ,
That likely means your sender id is not unique and that it matches many purchase id. Try this instead to return only the first value:
TransmitEcho =
VAR _Receivers = VALUES('Table'[Receiver])
RETURN
IF(
[Sender] in _Receivers,
VAR _ID = CALCULATE(MIN('Table'[ID]), FILTER('Table', 'Table'[Sender] = EARLIER('Table'[Receiver])), ALL('Table'[Receiver]))
VAR _Key =
SWITCH( TRUE(),
ISBLANK(_ID), BLANK(),
[ID] > _ID, COMBINEVALUES("^",_ID, [ID]),
COMBINEVALUES("^", [ID], _ID))
RETURN
_Key
)
If this doesn't work, if you can send some larger sample data for me to work with and I can test.
Hi @Sab ,
How about we create a calculated column first, and then count the total number of distinct relationships?
TransmitEcho =
VAR _Receivers = VALUES('Table'[Receiver])
RETURN
IF(
[Sender] in _Receivers,
VAR _ID = LOOKUPVALUE('Table'[ID], [Sender], [Receiver])
VAR _Key = IF([ID] > _ID, COMBINEVALUES("^",_ID, [ID]), COMBINEVALUES("^", [ID], _ID))
RETURN
_Key
)
A screenshot of the output is provided below:
And then, you'd create a basic distinctcount measure to find number of relationships:
CountTotalRelationship = DISTINCTCOUNTNOBLANK('Table'[TransmitEcho])
In this way, you can eventually evaluate and analyze your pairs.
Let me know if this helped you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |