cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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 :

 

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

Accepted Solutions
Highlighted
Frequent Visitor

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

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!

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

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

@KaterineCAA

 

Hi, try with:

 

Measure=DistinctCount(Table[No Garage])

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Highlighted
Frequent Visitor

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

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

Highlighted
Community Champion
Community Champion

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

@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




Lima - Peru
Highlighted
Frequent Visitor

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

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!

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors