topic Re: DAX Measure to count items where combined sum >= 10 in DAX Commands and Tips

DAX Measure to count items where combined sum >= 10

g_m_j — Thu, 16 Dec 2021 14:46:23 GMT

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:

Activity	Area_Name	Total
Care	Area_One	10
Care	Area_Two	6
Care	Area_Four	10
Hygiene	Area_One	10
Hygiene	Area_Two	9
Hygiene	Area_Two	2
Hygiene	Area_Two	3
Hygiene	Area_Two	10
Hygiene	Area_Three	1
Listening Activity	Area_One	10
Listening Activity	Area_Three	2

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 Output	Desired Output

Activity	Sep-21	Sep-21
Care Activity	3	2
Hygiene Activity	4	2
Attention Activity	0	0
Listening Activity	2	1

Thanks in advance 🙂

Re: DAX Measure to count items where combined sum >= 10

smpa01 — Thu, 16 Dec 2021 15:19:01 GMT

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 ) ) )

Re: DAX Measure to count items where combined sum >= 10

g_m_j — Thu, 16 Dec 2021 15:23:36 GMT

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.

Re: DAX Measure to count items where combined sum >= 10

smpa01 — Thu, 16 Dec 2021 15:27:25 GMT

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.

Re: DAX Measure to count items where combined sum >= 10

VahidDM — Fri, 17 Dec 2021 00:02:12 GMT

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:

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/

Re: DAX Measure to count items where combined sum >= 10

g_m_j — Mon, 20 Dec 2021 10:20:42 GMT

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.