Forum Discussion
Solved
Power Bi GroupBy count and Having
Hi, I am trying to do belwo in BI with measure : SELECT DISTINCT ID FROM <table1> WHERE <table1.active =1 and table1.id is not null > GROUP BY ID HAVING COUNT(DISTINCT <table.fieldx)>1 ...
Hey vipul03 ,
with a slight variation of the "the measure" the IDs will just be counted:
the measure just counting the ids = SUMX( ADDCOLUMNS( SUMMARIZE( FILTER( 'Table2' , 'Table2'[Active] = 1 ) , Table2[ID] ) , "dc" , [Distinct Count field 2] ) , var _dc = [dc] return IF(_dc > 1 , 1 , BLANK()) )Then it's possible to create something like this:
Regards,
Tom
Hi kentyler, here is the sample :
| ID | Active | field 2 |
| 1 | 0 | 0 |
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 1 | 1 | 2 |
| 3 | 1 | 1 |
| 4 | 0 | 1 |
| 3 | 1 | 0 |
| 5 | 1 | 1 |
| 6 | 0 | 0 |
Active is boolean. Field 2 is also boolean. We need to get distinct cout of ids where field 2 value count is more than 1
- TomMartens
Super User
Hey,
first I created this measure:Distinct Count field 2 = DISTINCTCOUNT('Table2'[field 2])then I created this measure, the final measure:
the measure = SUMX( ADDCOLUMNS( SUMMARIZE( FILTER( 'Table2' , 'Table2'[Active] = 1 ) , Table2[ID] ) , "dc" , [Distinct Count field 2] ) , var _dc = [dc] return IF(_dc > 1 , _dc , BLANK()) )This allows to create a table visual like so:
Wondering if this provides the expected result.
Regards,
Tom
