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

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