Forum Discussion

Mr_Glister's avatar
Mr_Glister
Icon for Advocate II rankAdvocate II
2 years ago
Solved

DAX distinct count with filter condition

I have an issue with something that I thought would be easy. 

 

Below is a sample of my data. I would want to create a DAX measure that counts the number of companies (distinct!) that have >= 10 Items sold per week. I would then want to show the DAX measure in a bar chart with the week number in the x-axis.

 

 

Highlighted in green are the values (summed up per Week) that I would want to count. 

 

Desired output:

 

 

 

I've tried the following, but that didn't work...

Count above 10 = CALCULATE( DISTINCTCOUNT( Table1[Company]), FILTER( Table1, SUM( Table1[Items sold]) >= 10))

 

I hope somebody can help me figure this out!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Mr_Glister ,

     

    I made simple samples and you can check the results below:

    Total Count = var _t = ADDCOLUMNS('Table',"total",SUMX(FILTER(ALL('Table'),[Week]=EARLIER([Week])&&[Company]=EARLIER([Company])),[Item sold]))
RETURN CALCULATE(DISTINCTCOUNT([Company]),FILTER(_t,[Total]>=10))

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • sayaliredij's avatar
    sayaliredij
    Icon for Solution Sage rankSolution Sage
    2 years ago

    HI Mr_Glister 

     

    Can you try following DAX instead?

    Count above 10 = CALCULATE( DISTINCTCOUNT( Table1[Company]), FILTER( ALL(Table1), SUM( Table1[Items sold]) >= 10))

     and let me know if it works

    Thanks,

    sayali

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Mr_Glister 

    item sold = SUM(table23[ Item sold]) 
     
     
    Count above 10 =
    var datasource =
    FILTER(
    ADDCOLUMNS(
            values(Table23[Company]),
            "sold" , [item sold]
    ),
    [sold] >=10
    )
    return

    COUNTROWS(datasource)
     
    let me know if you have any problem implementing it .
     
     
    If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos 👍🫡
    • Mr_Glister's avatar
      Mr_Glister
      Icon for Advocate II rankAdvocate II
      2 years ago

      Hi Daniel,

      is this supposed to be Sum() of item sold?

      "sold" , [item sold]

       

      • Daniel29195's avatar
        Daniel29195
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Mr_Glister 

        sorry for the late reply . it seems that there is a problem with the platform.  i m getting no notifications .

         

         

        correct. 

        this is sum(item sold) 

         

        let me know if you need any help .

         

         

         

        If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button 👍🤠

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Mr_Glister ,

     

    I made simple samples and you can check the results below:

    Total Count = var _t = ADDCOLUMNS('Table',"total",SUMX(FILTER(ALL('Table'),[Week]=EARLIER([Week])&&[Company]=EARLIER([Company])),[Item sold]))
RETURN CALCULATE(DISTINCTCOUNT([Company]),FILTER(_t,[Total]>=10))

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Share the download link of the PBI file or share data in a format that can be pasted in an MS Excel file.