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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mr_Glister
Advocate II
Advocate II

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.

 

Mr_Glister_0-1706186327864.png

 

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

Mr_Glister_1-1706186355647.png

 

Desired output:

Mr_Glister_2-1706186362688.png

 

 

 

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Mr_Glister ,

 

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

vtianyichmsft_1-1706238211651.png

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.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Mr_Glister ,

 

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

vtianyichmsft_1-1706238211651.png

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
Super User
Super User

@Mr_Glister 

Daniel29195_0-1706187834145.png

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 👍🫡

Hi Daniel,

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

"sold" , [item sold]

 

@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
Solution Sage
Solution Sage

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.