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

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
6 years ago

vipul03  do you mean this

 

Count := CALCULATE(COUNT('Table 1'[ID]))
DistinctCountMeasure:= IF([Count]>1, CALCULATE(DISTINCTCOUNT('Table 1'[ID]),VALUES('Table 1'[ID])),0)
vipul03's avatar
vipul03
Frequent Visitor
6 years ago

Hi smpa01 

basically, I need to get dictinct count of ID where Active is 1 and field2 value count is greater than 1

 

so for the given sample : 

 

result must count :

 

ID1,  will be counted , as active =1 and field 2 has three values 0 ,1 and 2

ID 2,  wil not be counted as field 2 has only one value

ID 3 will be counted as actuive =1 and field two has two values 0 and 1

 

IDs 4 ,5 and 6 won't be counted as they have just one value for field 2

 

 

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper 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.

    • 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
        Icon for Super User rankSuper 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