Forum Discussion

Sab's avatar
Sab
Icon for Helper V rankHelper V
4 years ago
Solved

Measure to count Reciprocal User Relationship

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!

 

Need Help
  • hnguy71's avatar
    hnguy71
    4 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

5 Replies

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    4 years ago

    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!

     

  • Sab's avatar
    Sab
    Icon for Helper V rankHelper V
    4 years ago

    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's avatar
      hnguy71
      Icon for Super User rankSuper User
      4 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.