Create measure of distinct count of a colomn based on distinct count of another column

[KaterineCAA]

Hi!

I'm trying to create a new measure that would show how many distinct places (based on GarageNo) have more than 50 distinct respondants (based on idResponse).

It seems very basic, but I can't find how to do it!

Here's a sample of my data :

idResponse	No garage
128	GRM10301
891	GRM11461
77	GRM15011
399	GRM10731
1340	GRQ15331
742	AM11321
772	GRQ15751
4	GRQ11181
383	GRM10731
804	GRQ13211
1168	GRM11861
771	GRM13941
228	GRM10601
918	GRQ15271
1103	GRM11861
1166	GRQ15271
591	GRQ15861

[Vvelarde]

@KaterineCAA

Hi, try with:

Measure=DistinctCount(Table[No Garage])

Regards

Victor

Lima - Peru

[KaterineCAA]

I don't want the distinct count of garages, but the distinct count of garages that has more than 50 respondants.

[Vvelarde]

@KaterineCAA

Hi, Ok.

Use a measure:

Measure =
VAR TempTable =
    SUMMARIZECOLUMNS (
        Table3[No garage];
        "COUNTOFIDS"; COUNT ( Table3[idResponse] )
    )
RETURN
    CALCULATE ( COUNTROWS ( FILTER ( TempTable; [COUNTOFIDS] > 50 ) ) )

Let me know if works

Regards

Victor

Lima-Peru

[KaterineCAA · Accepted solution]

Hello,

It didn't work.

I took a detour :

Created a new table :

Liste garage pour nombre répondants = 
	SUMMARIZE(
		'Profil des répondants';
		'Profil des répondants'[No garage];
		"Nombre de répondants";
		DISTINCTCOUNT('Profil des répondants'[idContact])) 

Create the measure afterwards :

Garages avec plus de 50 répondants = 
    CALCULATE (
	DISTINCTCOUNT(
		'Liste garage pour nombre répondants'[No garage]);
		'Liste garage pour nombre répondants'[Nombre de répondants]>=50)

And this works.

Thank you!