Forum Discussion

Fragnatic's avatar
Fragnatic
Frequent Visitor
9 years ago
Solved

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   ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

This measure would count the number of duplicate timestamps:

 

CALCULATE(COUNTA(Table1[timestamp]), ALLEXCEPT(Table1, Table1[timestamp]))-1
Fragnatic's avatar
Fragnatic
Frequent Visitor
9 years ago

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.