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

TomMartens's avatar
TomMartens
Super User
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

thanks for the response. I shall validate the results and will then mark this as solution.