topic Re: Count of ID with filters based on a different column - Values incorrect in DAX Commands and Tips

Count of ID with filters based on a different column - Values incorrect

SCallanan — Thu, 25 Jul 2024 15:56:41 GMT

Hi guys, Greg_Deckler or anyone who can help... 😕

first of all, thanks for having me...

my issue is the following:

I want to distinctcount the no of Trip Id's and filter by the inspection group.
The problem is that this does not add up. I know what the issue is. Some trips can be admin and physical, which are both under the same column header. (Same trip Id twice) In those cases only the physical trip but not the admin trip should be counted. I tried filtering on multiple criteria, but it does not work, probably because it is all under the same column header.

Any ideas how to fix this? Much obliged. Sandra

I can create a measure which counts if either of those criteria is applicable:

but when I try to create a measure that counts only the cases where both conditions apply, it does not work:

The measure is correct, but the card value shows blank even though there are cases that both criteria apply.

Re: Count of ID with filters based on a different column - Values incorrect

SachinNandanwar — Thu, 25 Jul 2024 18:44:40 GMT

Intially you mentioned that

"Some trips can be admin and physical, which are both under the same column header. (Same trip Id twice) In those cases only the physical trip but not the admin trip should be
counted."

and then you stated

"when I try to create a measure that counts only the cases where both conditions apply"

i think there are some discrepancies between the two.

Re: Count of ID with filters based on a different column - Values incorrect

adudani — Thu, 25 Jul 2024 20:23:19 GMT

Hello,

Kindly provide the sample input/output in a usable format (excel, csv, table etc.) masking sensitive information.

reference : https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150

Thanks,

Re: Count of ID with filters based on a different column - Values incorrect

SCallanan — Fri, 26 Jul 2024 14:57:46 GMT

Hi adudani ; SachinNandanwar

Thank you very much for your assistance. I've replied earlier to your message but somehow it looks like it was not saved.

I will try to explain the problem a bit better.

I want to count the number of trips. I use the following formula to do that.

Total Inspections = DISTINCTCOUNTNOBLANK('Inspections'[Trip Inspection Id])

AND I want to see how many inspections by catgory and group there are.

So the hierachy is as follows:

1. Inspection Group (Administrative / Physical)

2. Inspection Category (there is a list of different categories which are split into these two groups.

Now, the problem seems to be that the sum of inspections by inspection category is different than the sum by inspection group.

The reason that this problem happens is that per inspection visit (inspection ID) there can be a number of several categories included. For example: the inspector goes out to inspect 3 different categories. Two of them are Physical and 1 is Administrative. With the DistinctCount this is counted as 2 inspections but should only be one.

If there both Inspection Categories are present then this should be counted as a Physical Inspection type.

I have to somehow filter out the the Admin group when both categories apply.

Hope that makes sense. It's very hard to explain, but the short of it is that if both groups are included only the Physical Group should be counted for a particular inspection.

Thanks again, Sandra

Trip Inspection Id	Inspection Group	Inspection Category
7230829588	Administrative	Cross Checks
7230829588	Physical	Demersal Landings
7230829588	Physical	Multiannual Plan Species
7230829543	Administrative	Cross Checks
7230829543	Physical	Demersal Landings
7230829543	Physical	Multiannual Plan Species
7230600288	Administrative	Administrative/Non-Landing
7230555293	Administrative	Cross Checks