Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
This measure would count the number of duplicate timestamps:
CALCULATE(COUNTA(Table1[timestamp]), ALLEXCEPT(Table1, Table1[timestamp]))-1
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Without including message_ID we can't do, the reason is your timestamp have same name or value then how can we ?
@Fragnatic Sorry, but I don't understand your request.
So hopefully someone else will pick this up.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Does this variation on the measure from @ImkeF work for you:
Duplicate Message IDs = CALCULATE ( DISTINCTCOUNT ( Messages[message id] ), ALLEXCEPT ( Messages, Messages[timestamp] ) ) - 1
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |