cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
anni Helper I
Helper I

Count rows by several filters

Hi, 

Can someone advice on calculating users from Table 1 that use products from Table 3?

 

Tables are not related. But users are using several products, and I cannot filter the ones who use just one specific prod as in one table I have list of users - second table has price (and I dont need this table for this calculation - table three has all the different products. 
My goal is to calculate(distinctcount(Table1[Username], FILTER(Table3[Product] = "xx" && Table3[Product] <> "yy")) 

as to filter out the users that use only one type of several products. 

 

Count = CALCULATE(DISTINCTCOUNT(Table1'[Username]), FILTER('Table3', 'Table3'[Product] = "xx")) is currently returning me all users that have xx AND yy, how to get only the ones that have only "xx"? 

 

 

8 REPLIES 8
Super User IV
Super User IV

Re: Count rows by filter based on table without a relationship

If it 1to Many from table 3, 2 to 1 then it should happen. Else you might need bidirectional join.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


anni Helper I
Helper I

Re: Count rows by filter based on table without a relationship

I have Many to One for both connections as One to Many is not allowed.

 

Community Support
Community Support

Re: Count rows by several filters

Hi @anni ,

 

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
anni Helper I
Helper I

Re: Count rows by several filters

Hello @v-frfei-msft 
Here is the onedrive with my file:  https://1drv.ms/u/s!At5hcZNS2MLqnR49sjglvc5KnPN7?e=RnulYm

 

The goal is to have 2 measurements. 
1) Distinct count of Reseller that use Manufacturer "MaxiStuff" only - result should be 1 and I cannot get this result, 
2) Distinct count of Reseller that use any Manufacturers but not "MaxiStuff". The expected result here also should be 1, as MS is super popular. 

 

Hope you will have some clue about this! 🙂 

Community Support
Community Support

Re: Count rows by several filters

Hi @anni ,

 

Please update the measure as below.

NonMaxiStuff2 = calculate(DISTINCTCOUNT('MATERIAL'[ManufacturerName]),'MATERIAL'[non_MS]=0) 

 Capture.PNG

 

Pbix as attached.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
anni Helper I
Helper I

Re: Count rows by several filters

Hi @v-frfei-msft 

Thanks for the solution - it works when I want to count the total of nonMaxiStuff. 
However when I removed the filter in the Sheet3, Reseller display name, it went back to counting 8 resellers, that are using "only" MS. This should also have 1 in the result. 

 

 

anni Helper I
Helper I

Re: Count rows by several filters

@v-frfei-msft 

I guess it should have two filters but it's not allowing to: 

 

 
 

Capture.JPG

anni Helper I
Helper I

Re: Count rows by several filters

Also @v-frfei-msft 

 

I think the formula you wrote is counting the Manufacturer name while it really should be counting Resellers, as in a pie chart I want to show the number of resellers that use only the product or anything except the product Ms.

 

So in the pbi it gives 1 as a result because it counts only the MS as a product, but it is just a test sample and if I would have included 2 resellers that sell only Ms, the result of counting Manufacturer would still be 1!!

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors