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

TomMartens  thanks for the solution. I have additional question on this, what if I want to return set of IDs instead of count and then apply more filters for counting Ids on top. Can this measure also retturn set of ids/records?

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey vipul03 ,

 

I have to admit that I do not understand what you are requesting, but nevertheless this measure returns the IDs as a concatenated string:

the measure the IDs as set = 
CONCATENATEX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                FILTER(
                    'Table2'
                    , 'Table2'[Active] = 1
                )
                , Table2[ID]
            )
            , "dc" , CALCULATE([Distinct Count field 2] , ALL(Table2[field 2]))
        )
        , [dc] > 1
    )
    , [ID]
    , " | "
)

This allows to create to something like this:

I guess the measures are quite generic and can adapted to your likings. Regarding your question about additional filters, I think it should work, but w/o detailed knowledge of your data model, there might be some intricacies that I can't currently oversee.

Please provide examples of your expected result.

 

Regards,

Tom

 

  • vipul03's avatar
    vipul03
    Frequent Visitor
    6 years ago

    Thanks again TomMartens . Let me try the sample:

     

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

     

    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.

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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

  • vipul03's avatar
    vipul03
    Frequent Visitor
    6 years ago

    TomMartenslet me know if more details needed. Help is appreciated.