Forum Discussion

cvinas's avatar
cvinas
Frequent Visitor
8 years ago
Solved

Relationship problem

Hi all,

 

I have the following scenario:

 

 

 

 

 

 

 

 

 

 

 

 

I am counting the number of interactions from a sender to a destination and viceversa (depending on sender type: circle or central).  A user (central) can have different users in its circle (friends, sons, etc).

 

The measures are defined as follows:

 

- # Started by User  =  calculate(count('ActivityxUser'[Transaction]);'ActivityxUser'[Sender] = "CENTRAL")

- # Started by Circle  =  calculate(count('ActivityxUser'[Transaction]);'ActivityxUser'[Sender] = "CERCLE")

 

The graphs works fine when showing total interactions (above).

But, the behavior of the filter is not working as I expect. If I select ID Origin = 783, I would like to show in the graph also the interaction from the circle to this origin. But this is not what is happening:

 

 

 

 

 

How can I do to show in the graph the interactions started by Circle, which destination is the selected origin?

 

 

Thank you very much.

 

Regards,

 

Carlos.

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi cvinas,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    # Started by Circle = 
VAR currentSelectIDOrigin =
    MAX ( ActivityxUser[ID Origin] )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( ActivityxUser[ID Origin] ) )
            = COUNTROWS ( ALL ( ActivityxUser[ID Origin] ) ),
        CALCULATE (
            COUNT ( 'ActivityxUser'[Transaction] ),
            'ActivityxUser'[Sender] = "CERCLE"
        ),
        CALCULATE (
            COUNT ( ActivityxUser[Transaction] ),
            FILTER (
                ALLEXCEPT ( ActivityxUser, ActivityxUser[Date] ),
                ActivityxUser[ID Destination] = currentSelectIDOrigin
                    && ActivityxUser[Sender] = "CERCLE"
            )
        )
    )

     

    Regards

8 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi cvinas,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    # Started by Circle = 
VAR currentSelectIDOrigin =
    MAX ( ActivityxUser[ID Origin] )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( ActivityxUser[ID Origin] ) )
            = COUNTROWS ( ALL ( ActivityxUser[ID Origin] ) ),
        CALCULATE (
            COUNT ( 'ActivityxUser'[Transaction] ),
            'ActivityxUser'[Sender] = "CERCLE"
        ),
        CALCULATE (
            COUNT ( ActivityxUser[Transaction] ),
            FILTER (
                ALLEXCEPT ( ActivityxUser, ActivityxUser[Date] ),
                ActivityxUser[ID Destination] = currentSelectIDOrigin
                    && ActivityxUser[Sender] = "CERCLE"
            )
        )
    )

     

    Regards

    • cvinas's avatar
      cvinas
      Frequent Visitor
      8 years ago

      Hi v-ljerr-msft

       

      Reviewing the solution in detail I realized that it does work for the last aggregate level (Day level):

       

       

       

       

       

       

       

       

       

       

       

      Raw data in Excel is the following for sender:

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      And for destination:

       

       

       

       

      Why measure "#Started by Circle" always show 1 for Day Level of Date?

       

      I'll appreciate any help. 

       

      Thank you very much.

       

      Regards,

       

      Carlos

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi cvinas,

         

        Could you try the formula below to see if it works in your scenario? :smileyhappy:

        # Started by Circle =
VAR currentSelectIDOrigin =
    MAX ( ActivityxUser[ID Origin] )
VAR currentDate =
    MAX ( ActivityxUser[Date] )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( ActivityxUser[ID Origin] ) )
            = COUNTROWS ( ALL ( ActivityxUser[ID Origin] ) ),
        CALCULATE (
            COUNT ( 'ActivityxUser'[Transaction] ),
            'ActivityxUser'[Sender] = "CERCLE"
        ),
        CALCULATE (
            COUNT ( ActivityxUser[Transaction] ),
            FILTER (
                ALL ( ActivityxUser ),
                ActivityxUser[ID Destination] = currentSelectIDOrigin
                    && ActivityxUser[Sender] = "CERCLE"
                    && ActivityxUser[Date] = currentDate
            )
        )
    )

         

        Regards