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 ...
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

  • 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)