cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fragnatic Frequent Visitor
Frequent Visitor

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:
2017-01-02_20h25_53.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Steve_Wheeler Established Member
Established Member

Re: Count value pairs in different columns

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.

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Count value pairs in different columns

This measure would count the number of duplicate timestamps:

 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Fragnatic Frequent Visitor
Frequent Visitor

Re: Count value pairs in different columns

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.

Super User
Super User

Re: Count value pairs in different columns

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

So hopefully someone else will pick this up.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Steve_Wheeler Established Member
Established Member

Re: Count value pairs in different columns

@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
Baskar Super Contributor
Super Contributor

Re: Count value pairs in different columns

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

1.JPG

 

 

 

 

Fragnatic Frequent Visitor
Frequent Visitor

Re: Count value pairs in different columns

Thank you very much @Steve_Wheeler, 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!

Steve_Wheeler Established Member
Established Member

Re: Count value pairs in different columns

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.

View solution in original post

Fragnatic Frequent Visitor
Frequent Visitor

Re: Count value pairs in different columns

Thanks again Steve!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)