Power Bi GroupBy count and Having

[vipul03]

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
ORDER BY ID

I am trying to do the same using following but not able to add the filter which I am doing with Having in above sql statement :

Measure1 = CALCULATE(

              DISTINCTCOUNT(Table1[ID]),

                   GROUPBY(Table1,Table1[ID]),FILTER('Table1', Table1[Active] = TRUE() && Table1[ID] <> BLANK() )

Kinldy helpout.

[smpa01]

@vipul03  can you please provide a sample data

[vipul03]

Hi kentyler, here is the sample :

ID	Active	field 2
1	0	0
1	1	0
1	1	1
2	1	1
1	1	2
3	1	1
4	0	1
3	1	0
5	1	1
6	0	0

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

[smpa01]

@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]

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 · Accepted solution]

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]

@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]

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]

Thanks again @TomMartens . Let me try the sample:

ID	Active	field 2
1	0	0
1	1	0
1	1	1
2	1	1
1	1	2
3	1	1
4	0	1
3	1	0
5	1	1
6	0	0

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.

[vipul03]

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

[TomMartens]

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]

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

[vipul03]

sorrysample is asked by @smpa01 

[TomMartens]

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]

@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 ()
)

[kentyler]

Someone else also had this question: https://community.powerbi.com/t5/Desktop/Group-by-and-Having/td-p/448502  basically you can use the DAX function SUMMARIZE() to mimic the SQL Group by and Having clauses

[vipul03]

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)