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
g_m_j
Frequent Visitor

DAX Measure to count items where combined sum >= 10

Hi all,

 

Been battling this one for too long so I'm seeking help here.

 

I have data made up of Area based Activity requirements.

 

There are 7 different areas.

 

If an Area completes an Activity 10 times (either in one go, or across multiple attempts), then they are good (i.e. green on my RAG).

 

I then need to count the "good" areas.

 

Example Areas:

Area_Name
Area_One
Area_Two
Area_Three
Area_Four
Area_Five
Area_Six
Area_Seven

 

Here is sample data:

ActivityArea_NameTotal
CareArea_One10
CareArea_Two6
CareArea_Four10
HygieneArea_One10
HygieneArea_Two9
HygieneArea_Two2
HygieneArea_Two3
HygieneArea_Two10
HygieneArea_Three1
Listening ActivityArea_One10
Listening ActivityArea_Three2

 

Here's the DAX I currently have, but unfortunately it just seems to count the distinct Area_Names for all (whereas I only want to count them if they have >= 10)

Distinct_Area_Count_10x = CALCULATE(DISTINCTCOUNT(Report_Summary[Area_Name]),
FILTER(ALL('Activity_Breakdown'[Total]),
SUM('Activity_Breakdown'[Total])>=10))+0

 

Here is the output I am getting from the above, and the desired outcome I would like to achieve:

 

 Current OutputDesired Output
   
ActivitySep-21Sep-21
Care Activity32
Hygiene Activity42
Attention Activity00
Listening Activity21

 

Thanks in advance 🙂

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

Hi @g_m_j 

 

Try this measure:

Distinct_Area_Count_10x =
VAR _A =
    FILTER (
        SUMMARIZE (
            Activity_Breakdown,
            Activity_Breakdown[Activity],
            Report_Summary[Area_Name],
            "Total", SUM ( Activity_Breakdown[Total] )
        ),
        [Total] >= 10
    )
RETURN
    COUNTROWS ( _A )

 

output:

VahidDM_0-1639699320079.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

5 REPLIES 5
VahidDM
Super User
Super User

Hi @g_m_j 

 

Try this measure:

Distinct_Area_Count_10x =
VAR _A =
    FILTER (
        SUMMARIZE (
            Activity_Breakdown,
            Activity_Breakdown[Activity],
            Report_Summary[Area_Name],
            "Total", SUM ( Activity_Breakdown[Total] )
        ),
        [Total] >= 10
    )
RETURN
    COUNTROWS ( _A )

 

output:

VahidDM_0-1639699320079.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

g_m_j
Frequent Visitor

This works perfectly, thank you! 

I had a feeling SUMMARIZE might be involved somehow, but haven't used it before - so is great to see it in action. Thanks again.

smpa01
Super User
Super User

@g_m_j  can you try this measure

 

 

Measure =
CALCULATE (
    DISTINCTCOUNT ( Activity_Breakdown[Area_Name] ),
    CALCULATETABLE (
        Activity_Breakdown,
        FILTER (
            Activity_Breakdown,
            CALCULATE (
                SUM ( Activity_Breakdown[Total] ),
                ALLEXCEPT (
                    Activity_Breakdown,
                    Activity_Breakdown[Activity],
                    Activity_Breakdown[Area_Name]
                )
            ) >= 10
        )
    )
)

 

 

smpa01_0-1639667397707.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
g_m_j
Frequent Visitor

That didn't quite do it unfortunately. Only a slight change in the measure results (from 3, 4, 2 to 3, 4, 1).

 

However, I can see from your results that your version works as expected.

 

 

@g_m_j  I acted on the sample you gave and it clearly worked there as evident in the attached pbix. If you have a difreent sample, then revise your question.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.