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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

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! 🙂 

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

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!!

 

 

Anonymous
Not applicable

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. 

 

 

Anonymous
Not applicable

@v-frfei-msft 

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

 

 
 

Capture.JPG

amitchandak
Super User
Super User

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

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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