Count rows by several filters

[Anonymous]

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"? 

[v-frfei-msft]

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.

[Anonymous]

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

[v-frfei-msft]

Hi @Anonymous ,

Please update the measure as below.

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

 

Pbix as attached.

[Anonymous]

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]

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]

@v-frfei-msft 

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

[amitchandak]

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

[Anonymous]

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