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

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;

     

    Thank you very much!! It works very nice!

     

    Regards,

     

    Carlos

  • 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

      • cvinas's avatar
        cvinas
        Frequent Visitor
        8 years ago

        Hi v-ljerr-msft,

         

        I changed the formula and now,  no value is plotted:

         

         

         

         

         

         

         

         

         

         

         

         

         

        If needed, I can upload the .pbix file.

         

        Thank you very much.

         

        Carlos