Forum Discussion

vipul03's avatar
vipul03
Frequent Visitor
6 years ago
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
ORDER BY ID

 

I am trying to do the same using following but not able to add the filter which I am doing with Having in above sql statement :

 

Measure1 = CALCULATE(
              DISTINCTCOUNT(Table1[ID]),
                   GROUPBY(Table1,Table1[ID]),FILTER('Table1', Table1[Active] = TRUE() && Table1[ID] <> BLANK() )

Kinldy helpout.

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    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

16 Replies

    • vipul03's avatar
      vipul03
      Frequent Visitor
      6 years ago

      sorry but this one doesn't work. Also, as called out I need filtered on second condition as well(whioch is mentioned in having, that's counting different value in the other field)

    • vipul03's avatar
      vipul03
      Frequent Visitor
      6 years ago

      Hi kentyler, here is the sample :

       

      IDActivefield 2
      100
      110
      111
      211
      112
      311
      401
      310
      511
      600

       

      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