topic Re: Distinct Count filter with sum in DAX Commands and Tips

Distinct Count filter with sum

bcardenas — Tue, 11 Aug 2020 00:47:22 GMT

Hello,

I am a newbie trying to count Lenders w/ 4 or more certs for a year. I am trying this formula but it does not filter >3, it just gives me a straight count.

Active Lenders 1 yr = CALCULATE(

DISTINCTCOUNT(Certificates[institutionID]),

FILTER(Certificates,(SUM(Certificates[Certified]) > 3)))

Re: Distinct Count filter with sum

lbendlin — Tue, 11 Aug 2020 00:50:10 GMT

you are missing the year filter, and the ALL* context modifier.

Provide some sample data and expected outcome.

Re: Distinct Count filter with sum

amitchandak — Tue, 11 Aug 2020 02:27:46 GMT

bcardenas , Try like

Active Lenders 1 yr = countX(filter(summarize(Certificates,Certificates[Lenders],"_1", CALCULATE(
DISTINCTCOUNT(Certificates[institutionID]))),[_1]>3),[Lenders])

Re: Distinct Count filter with sum

AntrikshSharma — Tue, 11 Aug 2020 05:24:27 GMT

bcardenas Try this:

Active Lenders 1 yr = CALCULATE ( DISTINCTCOUNT ( Certificates[institutionID] ), FILTER ( ALL ( Certificates ), CALCULATE ( SUM ( Certificates[Certified] ) ) > 3 ) )

Re: Distinct Count filter with sum

bcardenas — Tue, 11 Aug 2020 12:17:03 GMT

lbendlin I would prefer to not have the year filter in the measure.

Data -

InstitutionID	certified	cert_date
1	1	1/1/2020
1	1	2/5/2020
1	1	2/8/2020
1	1	3/10/2020
2	1	1/20/2020
2	1	1/26/2020
2	1	3/1/2020
3	1	4/1/2002
1	1	1/1/2019
1	1	2/5/2019
1	1	2/8/2019
1	1	3/10/2019
2	1	1/20/2019
2	1	1/26/2019
2	1	3/1/2019
2	1	3/2/2019
3	1	4/1/2019

Outcome -

YEAR	Instiution Count	Inst > 3Certs
2020	3	1
2019	3	2

Re: Distinct Count filter with sum

bcardenas — Tue, 11 Aug 2020 12:16:16 GMT

AntrikshSharma when I try this solution I do not get an error but the measure is blank when pulled into a report.

Re: Distinct Count filter with sum

AntrikshSharma — Tue, 11 Aug 2020 12:47:34 GMT

bcardenas Sorry, I didn't see that you have posted the data, you can try this:

Total Certificate = SUM ( Certificates[certified] )

Active Lenders 1 yr = COUNTROWS ( FILTER ( SUMMARIZE ( Certificates, Certificates[InstitutionID], 'Date'[Year] ), [Total Certificate] > 3 ) )

Re: Distinct Count filter with sum

bcardenas — Tue, 11 Aug 2020 13:28:26 GMT

AntrikshSharma Much thanks, this is exactly what I needed.

Re: Distinct Count filter with sum

lbendlin — Tue, 11 Aug 2020 14:00:16 GMT

"I would prefer to not have the year filter in the measure."

That is impossible, as you want the year grouping in your output.

Note - there is a typo in your sample data . I guess you meant 4/1/2020 ?

Here is a version that uses AntrikshSharma 's approach but does not require an external measure, and doesn't require the redundant column.

GT3 = var s = summarize(Certificates,Certificates[InstitutionID],Dates[Yearcode],"ct",count(Certificates[cert_date])) var c = countrows(filter(s,[ct]>3)) return c