Measure to count Reciprocal User Relationship

[Sab]

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!

[hnguy71 · Accepted 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.

[Sab]

Thanks for all your help!

[Ashish_Mathur · Accepted solution]

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.

[Sab]

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.'

[hnguy71 · Accepted 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.

[hnguy71]

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!