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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Get count of column based on values in multiple other columns

I'm stuck trying to get a count of values in a column that depends on several other columns.

 

I have a table with many Item Numbers. Each Item Number appears in several Locations and within each location it can have a different Type and different Supplier. None of the columns are related. For each Item Number I need the count of suppliers for that location, but I need to ignore any rows with Type = X. 

 

Item NumberLocationTypeSupplier*Expected*
Supplier Count
6240A1X1231
6240A1P1111
6240A1T1111
6240E2X1112
6240E2P1232
6240E2P1112

 

Ultimately, I'm also trying to get a measure with the number of items that have more than one supplier, in the example above the answer is 1.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , A new column

calculate(distinctcount(Table[Supplier]), filter(Table, [Item Number] = earlier([Item Number]) && [Location] = earlier([Location]) && [Type]<> "X"))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , A new column

calculate(distinctcount(Table[Supplier]), filter(Table, [Item Number] = earlier([Item Number]) && [Location] = earlier([Location]) && [Type]<> "X"))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Thanks, that worked. EARLIER is what I was missing, I have some reading to do!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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