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
Thanks again TomMartens . Let me try 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 |
So measure 1 already priovide count based on the conditions.
Measure 2 is expected to return records that has field 2 as value 1 on top of the records that were counted in measure 1. Because, measure 1 alos counted teh records that has field value 0 and 2 as well.
Hey vipul03 ,
I still do not understand what your expected result should look like, I will reread tonight, guess I need some sleep now.
Regards,
Tom