Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

 

Need Help

8 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Please update the measure as below.

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

         

         

        Pbix as attached.

         

         

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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