Relationship problem

[cvinas]

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.

[v-ljerr-msft · Accepted solution]

Hi @cvinas,

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

# 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]

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]

Hi @cvinas,

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

# 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]

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

[v-ljerr-msft]

Hi @cvinas,

Yes, please upload the pbix file, so that I can try to investigate on the issue. Do mask sensitive data before uploading. 

Regards

[cvinas]

Hi @v-ljerr-msft,

Here you have! Sensitive information is masked!

https://drive.google.com/file/d/0BxhJHCX2SRyJYUdhNEQzdmhCMTA/view?usp=sharing

Thank you!

[cvinas]

Hi @v-ljerr-msft,

Reviewing in more detail the solution, it works partially well. The problem is that the filter ignore the dates that are not equal to the central user dates, which also can contain a response from circle.

For example: Origen User 942 send transactions in days 02/08, 03/08, 04/08 and 14/08: 

It receives responses on that days, but also in day 08/08:

I suppose this is not taken into account in the new formula, that's why it not appears in the graph:

But also, in the aggregation (month -level), the counting is just referenced to the last date (14/08/2017):

Started per User is OK,  but, total for Circle is 17, and it is taking the last value for the max date.

I would appreciate any help !

Thank you!

Carlos

[cvinas]

Hi @v-ljerr-msft;

Thank you very much!! It works very nice!

Regards,

Carlos