topic Re: DAX Measure - count within a grouping number that meet a criteria in DAX Commands and Tips

DAX Measure - count within a grouping number that meet a criteria

RowenaJones — Thu, 27 Mar 2025 14:44:40 GMT

Hi
I want a measure (to add to a card) that gives me the number of distinct organisations that have income less than expected above a user set criteria for more than three instances;
I have succesfully got a measure that goves me the number of distinct organisations that have one or more instances that meet the criteria, but I now want something that tells me how many have three or more;

My successful measures are:

M_%IlessE_3 = calculate( distinctcount (DeclarationAssessment[Contractor] ), filter((ALLEXCEPT('DeclarationAssessment',

DeclarationAssessment[Contractor],

DeclarationAssessment[MonthStart])),DeclarationAssessment[FlagIlessthanE]=1 && DeclarationAssessment[IlessthanE%]>='Percentage Threshold'[Percentage Threshold Value]))

** this gives me number of contractors that have at least one month where income less than threshold set;

M_MonthsPerContractorBelowThreshold =

SUMX(

GROUPBY(

FILTER(

DeclarationAssessment,

[FlagIlessthanE] = 1 &&

[IlessthanE%] >= 'Percentage Threshold'[Percentage Threshold Value]

DeclarationAssessment[Contractor],

DeclarationAssessment[MonthStart]

)

** this gives me the number of months per contractor that meet the threshold

I've tried the below, but it doesn't give me the correct result

M_ilessE3plusMeetsThreshold =

VAR ContractorMonths =

FILTER(

ADDCOLUMNS(

SUMMARIZE(DeclarationAssessment,

DeclarationAssessment[Contractor],

DeclarationAssessment[MonthStart]

"@MeetsThreshold",

MAX(DeclarationAssessment[IlessthanE%]) >= 'Percentage Threshold'[Percentage Threshold Value]

[@MeetsThreshold] = TRUE

)

VAR ContractorCounts =

ADDCOLUMNS(

SUMMARIZE(ContractorMonths, DeclarationAssessment[Contractor]),

"@ThresholdMonths",

COUNTROWS(FILTER(ContractorMonths, DeclarationAssessment[Contractor] = EARLIER(DeclarationAssessment[Contractor])))

)

RETURN

CALCULATE(

DISTINCTCOUNT(DeclarationAssessment[Contractor]),

FILTER(ContractorCounts, [@ThresholdMonths] > 3)

)

I'm new to DAX; any help very much appreciated 🙂

Re: DAX Measure - count within a grouping number that meet a criteria

bhanu_gautam — Thu, 27 Mar 2025 14:52:54 GMT

RowenaJones , Try using

DAX
M_ilessE3plusMeetsThreshold =
VAR ContractorMonths =
FILTER(
ADDCOLUMNS(
SUMMARIZE(
DeclarationAssessment,
DeclarationAssessment[Contractor],
DeclarationAssessment[MonthStart]
),
"@MeetsThreshold",
DeclarationAssessment[FlagIlessthanE] = 1 &&
DeclarationAssessment[IlessthanE%] >= 'Percentage Threshold'[Percentage Threshold Value]
),
[@MeetsThreshold] = TRUE
)

VAR ContractorCounts =
ADDCOLUMNS(
SUMMARIZE(
ContractorMonths,
DeclarationAssessment[Contractor]
),
"@ThresholdMonths",
COUNTROWS(
FILTER(
ContractorMonths,
DeclarationAssessment[Contractor] = EARLIER(DeclarationAssessment[Contractor])
)
)
)

RETURN
CALCULATE(
DISTINCTCOUNT(DeclarationAssessment[Contractor]),
FILTER(
ContractorCounts,
[@ThresholdMonths] > 3
)
)

Re: DAX Measure - count within a grouping number that meet a criteria

Anonymous — Wed, 02 Apr 2025 12:00:29 GMT

Hi RowenaJones,

Thanks bhanu_gautam for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

Re: DAX Measure - count within a grouping number that meet a criteria

RowenaJones — Wed, 02 Apr 2025 13:33:23 GMT

Hi, thanks bhanu_gautam; I initially got an error ('A single value for column 'FlagIlessthanE' in table 'DeclarationAssessment' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.') which I got around by adding Max(DeclarationAssessment[FlagIlessthanE]) and the same for [IlessthanE%]; but gave the result of 145 contractors and I know that the number that have more than three months that are more than meet or exceed the threshold is 22;

Thank you for your help though 🙂

Re: DAX Measure - count within a grouping number that meet a criteria

Anonymous — Thu, 03 Apr 2025 10:22:13 GMT

Hi RowenaJones,

your logic might be counting incorrectly.
may be COUNTROWS(ContractorMonths) might be over-counting or missing valid months due to improper filtering.

Regards,

Vinay Pabbu

Re: DAX Measure - count within a grouping number that meet a criteria

RowenaJones — Thu, 03 Apr 2025 11:07:50 GMT

Thanks Vinay
I (with some advice from AI) have resolved using two measures:
M_MonthsAboveThreshold =
VAR MonthsAboveThreshold =
SUMX(
FILTER(DeclarationAssessment, DeclarationAssessment[M_%IEThresholdMet] = 1),
1
)
RETURN
MonthsAboveThreshold

M_ContractorsAbove3Months =
VAR ContractorsAboveThreshold =
SUMX (
VALUES(DeclarationAssessment[Contractor]),
IF(
CALCULATE(
COUNTROWS(FILTER(DeclarationAssessment, DeclarationAssessment[M_%IEThresholdMet] = 1))
) > 3,
1,
0
)
)
RETURN
ContractorsAboveThreshold

Re: DAX Measure - count within a grouping number that meet a criteria

Anonymous — Thu, 03 Apr 2025 11:11:07 GMT

RowenaJones Thanks for sharing, Can you please accept your own post as solution it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu