DAX distinct count with filter condition

[Mr_Glister]

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!

[Ashish_Mathur]

Hi,

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

[Anonymous · Accepted solution]

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.

[Daniel29195]

@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]

Hi Daniel,

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

"sold" , [item sold]

[Daniel29195]

@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 👍🤠

[sayaliredij]

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