Forum Discussion
Count value pairs in different columns
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
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] ) ) - 1Note 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.
8 Replies
This measure would count the number of duplicate timestamps:
CALCULATE(COUNTA(Table1[timestamp]), ALLEXCEPT(Table1, Table1[timestamp]))-1
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 device100 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.
