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
Community Champion
6 years ago

vipul03  can you please provide a sample data

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

  • vipul03's avatar
    vipul03
    Frequent Visitor
    6 years ago

    sorrysample is asked by smpa01 

    • TomMartens's avatar
      TomMartens
      Super User
      6 years ago

      Hey,

      first I created this measure:

      Distinct Count field 2 = DISTINCTCOUNT('Table2'[field 2])

      then I created this measure, the final measure:

      the measure = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                'Table2'
                , 'Table2'[Active] = 1
            )
            , Table2[ID]
        )
        , "dc" , [Distinct Count field 2]
    )
    , 
    var _dc = [dc]
    return
    IF(_dc > 1  , _dc , BLANK())
)

      This allows to create a table visual like so:


      Wondering if this provides the expected result.

       

      Regards,

      Tom

       

      • smpa01's avatar
        smpa01
        Community Champion
        6 years ago

        vipul03  not sure if you are looking for this

         

        DesiredCount: = 
IF (
    CALCULATE (
        COUNT ( 'Table 1'[field 2] ),
        KEEPFILTERS ( FILTER ( ALL ( 'Table 1'[Active] ), 'Table 1'[Active] = 1 ) )
    ) > 1,
    DISTINCTCOUNT ( 'Table 1'[ID] ),
    BLANK ()
)
  • smpa01's avatar
    smpa01
    Community 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
        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