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

[RowenaJones]

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]

    ),

    1

)

** 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 🙂

[RowenaJones]

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 🙂

[Anonymous]

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

[RowenaJones · Accepted solution]

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

[Anonymous]

@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

[Anonymous]

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

[bhanu_gautam]

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