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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Fragnatic
Frequent Visitor
9 years ago

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's avatar
    Anonymous
    Not applicable
    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.

    • Fragnatic's avatar
      Fragnatic
      Frequent Visitor
      9 years ago
      Thanks again Steve!