Count value pairs in different columns

[Fragnatic]

Hello folks,

i have a question regarding the identification and count of value pairs in different colums.

My table looks like this:

timestamp         message id     device

1483382200          10                 A

1483382200          10                 A

1483382300          5                   B
1483382300          5                   B

1483382400          6                   B

1483382500          100               C

1483382600          11                 A

1483382700           7                  B

1483382700           8                  B

1483382800           12                A

What i am trying to find out is the count of timestamps where the message id is different.
There should only be one timestamp linked to one message id. If there are two rows where the timestamp and the message id are the same it's perfectly fine. I just need to detect if one timestamp is linked to multiple message id's.

Ideally, the count should be 2 if one timestamp is linked to 3 different message id's.

I calculated an additional column where i concentrated the timestamp and the message id into a new number and created a visual table. I used a specific timestamp as a filter and i got this result:


It's almost what i was looking for altough the count should be 2 and i need it as a measure.
Thank you for your help and advice in advance!

Best Regards

[ImkeF]

This measure would count the number of duplicate timestamps:

CALCULATE(COUNTA(Table1[timestamp]), ALLEXCEPT(Table1, Table1[timestamp]))-1

[Fragnatic]

Thank you very much for your time and effort Imke!
The formula you provided and the number of duplicate values is absolutely correct.

However, do you have any suggestion on how to calculate only the events i described? One timestamp wich is linked to several message IDs?
Or do you know the DAX syntax behind the visual table i posted? The filters there are correct and i just used drag & drop. What does Power BI do in the background?

Imagine this case:

timestamp          message id          device

100                         1                          A

100                         1                          A

100                         2                          A  

100                         2                          A

100                         3                          A

100                         3                          A

I would love to discover that there are 3 different message IDs connected to the same timestamp and add 2 to my total count.

[Baskar]

Without including message_ID we can't do, the reason is your timestamp have same name or value then how can we ?

[ImkeF]

@Fragnatic Sorry, but I don't understand your request.

So hopefully someone else will pick this up.

[Anonymous]

@Fragnatic,

Does this variation on the measure from @ImkeF work for you:

Duplicate Message IDs =
CALCULATE (
    DISTINCTCOUNT ( Messages[message id] ),
    ALLEXCEPT ( Messages, Messages[timestamp] )
)
    - 1

[Fragnatic]

Thank you very much @Anonymous, this is exactly what i needed.
I spent hours trying to figure this out.

It works perfectly if i use a filter to specify a timestamp. It also works without a filter at all.
But it seems that i can't filter by device any more, probably because of the ALLEXCEPT function.

Is there even a way to filter this measure by device?

Many thanks again!

[Anonymous · Accepted solution]

Correct - the ALLEXCEPT function is ignoring all filters except the row context for [timestamp]

You can allow a filter by Message[device] by adding it to the ALLEXCEPT:

Duplicate Message IDs = 
CALCULATE (
    DISTINCTCOUNT ( Messages[message id] ),
    ALLEXCEPT ( Messages, Messages[timestamp], Messages[device] )
)
- 1

Note that the filter/slicer must be on Messages[device] - if you want to filter on [device] from a related dimension table, that would likely change the approach required.

[Fragnatic]

Thanks again Steve!