Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
SCallanan
Frequent Visitor

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

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  

 

SCallanan_1-1721920739667.png

SCallanan_2-1721920942404.png

 

 

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

SCallanan_1-1721922685617.png

SCallanan_4-1721922896932.png

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

SCallanan_2-1721922798159.png

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

SCallanan_3-1721922858507.png

 

 

 

3 REPLIES 3
adudani
Super User
Super User

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/1447... 

Thanks,

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

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

SCallanan_0-1722005694129.png

 

Trip Inspection IdInspection GroupInspection Category
7230829588AdministrativeCross Checks
7230829588PhysicalDemersal Landings
7230829588PhysicalMultiannual Plan Species
7230829543AdministrativeCross Checks
7230829543PhysicalDemersal Landings
7230829543PhysicalMultiannual Plan Species
7230600288AdministrativeAdministrative/Non-Landing
7230555293AdministrativeCross Checks
SachinNandanwar
Super User
Super User

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.



Regards,
Sachin
Check out my Blog

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.