How use distinctcount in a groupby

[Cobra77]

HI

following the question:

https://community.powerbi.com/t5/Desktop/how-to-calculate-the-number-of-people-logged-in-for-so-many...

The count is not good if the aggregation with product

How can i use disctinctcount FK_calendrier by people ( with or wihtout product filtered or not )

User By Bucket 3:=
VAR Nbdays =
IF (
HASONEVALUE ( '80 - Slicer Numbers Days'[NbDays] );
VALUES ( '80 - Slicer Numbers Days'[NbDays] );
30
)
VAR DateSelected =
YEAR ( LASTDATE ( '01 - Calendrier'[Date] ) ) * 10000
+ MONTH ( LASTDATE ( '01 - Calendrier'[Date] ) ) * 100
+ DAY ( LASTDATE ( '01 - Calendrier'[Date] ) )
VAR DateMin =
YEAR ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) ) * 10000
+ MONTH ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) ) * 100
+ DAY ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) )
RETURN
IF (
HASONEVALUE ( buckets[value] );
CALCULATE (
COUNTX (
GROUPBY (
CT_Microsoft;
CT_Microsoft[Microsoft_userName];
"Nb Cnx"; COUNTX ( CURRENTGROUP (); CT_Microsoft[Microsoft_FK_IdCalendrier] )
);
[Nb Cnx]
);
CT_Microsoft[Microsoft_FK_IdCalendrier] >= DateMin
&& CT_Microsoft[Microsoft_FK_IdCalendrier] <= DateSelected;
FILTER (
GROUPBY (
FILTER (
CT_Microsoft;
CT_Microsoft[Microsoft_FK_IdCalendrier] >= DateMin
&& CT_Microsoft[Microsoft_FK_IdCalendrier] <= DateSelected
);
CT_Microsoft[Microsoft_userName];
"Nb Cnx"; COUNTX ( CURRENTGROUP (); CT_Microsoft[Microsoft_FK_IdCalendrier] )
);
[Nb Cnx] = VALUES ( buckets[value] )
)
);
BLANK ()
)

Thanks for your help

[amitchandak]

@Cobra77 ,Can you share sample data and sample output in table format?

[Cobra77]

with axe :

buckets = GENERATESERIES(1, 200) 

User By Bucket 3:=
VAR Nbdays =
    IF (
        HASONEVALUE ( '80  - Slicer Numbers Days'[NbDays] );
        VALUES ( '80  - Slicer Numbers Days'[NbDays] );
        30
    )
VAR DateSelected =
    YEAR ( LASTDATE ( '01 - Calendrier'[Date] ) ) * 10000
        + MONTH ( LASTDATE ( '01 - Calendrier'[Date] ) ) * 100
        + DAY ( LASTDATE ( '01 - Calendrier'[Date] ) )
VAR DateMin =
    YEAR ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) ) * 10000
        + MONTH ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) ) * 100
        + DAY ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) )
RETURN
    IF (
        HASONEVALUE ( buckets[value] );
        CALCULATE (
            COUNTX (
                GROUPBY (
                    CT_Microsoft;
                    CT_Microsoft[Microsoft_userName];
                    "Nb Cnx"; COUNTX ( CURRENTGROUP (); CT_Microsoft[Microsoft_FK_IdCalendrier] )
                );
                [Nb Cnx]
            );
            CT_Microsoft[Microsoft_FK_IdCalendrier] >= DateMin
                && CT_Microsoft[Microsoft_FK_IdCalendrier] <= DateSelected;
            FILTER (
                GROUPBY (
                    FILTER (
                        CT_Microsoft;
                        CT_Microsoft[Microsoft_FK_IdCalendrier] >= DateMin
                            && CT_Microsoft[Microsoft_FK_IdCalendrier] <= DateSelected
                    );
                    CT_Microsoft[Microsoft_userName];
                    "Nb Cnx"; COUNTX ( CURRENTGROUP (); CT_Microsoft[Microsoft_FK_IdCalendrier] )
                );
                [Nb Cnx] = VALUES ( buckets[value] )
            )
        );
        BLANK ()
    )

with this data :

toto@microsoft.Com  ;  20200815 ;  Product 1 ; 25
toto@microsoft.Com  ;  20200815 ;  Product 2 ; 3
toto@microsoft.Com  ;  20200816 ;  Product 1 ; 2
toto@microsoft.Com  ;  20200822 ;  Product 1 ; 24
toto@microsoft.Com  ;  20200818 ;  Product 2 ; 25
toto@microsoft.Com  ;  20200825 ;  Product 1 ; 36
toto@microsoft.Com  ;  20200825 ;  Product 2 ; 34

titi@microsoft.Com  ;  20200813 ;  Product 1 ; 32
titi@microsoft.Com  ;  20200815 ;  Product 3 ; 3
titi@microsoft.Com  ;  20200816 ;  Product 1 ; 22
titi@microsoft.Com  ;  20200817 ;  Product 3 ; 2
titi@microsoft.Com  ;  20200820 ;  Product 3 ; 2
titi@microsoft.Com  ;  20200821 ;  Product 1 ; 22
titi@microsoft.Com  ;  20200828 ;  Product 2 ; 5
titi@microsoft.Com  ;  20200828 ;  Product 3 ; 5

tUtU@microsoft.Com  ;  20200807 ;  Product 1 ; 45
tUtU@microsoft.Com  ;  20200818 ;  Product 3 ; 27
tUtU@microsoft.Com  ;  20200828 ;  Product 2 ; 26

tiUUti@microsoft.Com  ;  20200811 ;  Product 1 ; 4
tiUUti@microsoft.Com  ;  20200817 ;  Product 3 ; 4
tiUUti@microsoft.Com  ;  20200820 ;  Product 1 ; 6

with this little example, that would give ,

if the product is not filtered , column stack with 3 columns in this case :
- 2 people connected 3 times
- 1 people connected 7 times
- 1 people connected 5 times

if product 1 is filtered , column stack with 4 columns in this case :

- 1 people connected 4 times
- 1 people connected 3 times
- 1 people connected 2 times

- 1 people connected 1 times

thanks

[v-yiruan-msft]

Hi @Cobra77 ,

You can create 2 measures as below:

CountofPerson =
VAR _selpro =
    ALLSELECTED ( 'Table'[Product] )
VAR _countofp =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Mail] ),
        FILTER ( 'Table', 'Table'[Product] IN _selpro )
    )
RETURN
    _countofp

Connect Times =
VAR _selpro =
    ALLSELECTED ( 'Table'[Product] )
VAR _countofT =
    CALCULATE (
        COUNT ( 'Table'[Mail] ),
        FILTER ( 'Table', 'Table'[Product] IN _selpro )
    )
RETURN
    _countofT

Best Regards

Rena

[Cobra77]

Hi @amitchandak 

thanks for your answer but this is not quite the expected result.

It s just a indicator , in a stacked column , with a dynamic Axe ( with generateserie ... )

the given indicator is good when the aggregate table does not contain the products.
I would like to be able to make a distinctcount of FK_Idcalendrier with or without filtered product

See also the first link

thanks.

[Cobra77 · Accepted solution]

Hi @amitchandak , @v-yiruan-msft 

i succeeded , change groupby by summarize and filter by calculatetable :

User By Bucket :=
VAR Nbdays =
    IF (
        HASONEVALUE ( '80  - Slicer Numbers Days'[NbDays] );
        VALUES ( '80  - Slicer Numbers Days'[NbDays] );
        30
    )
VAR DateSelected =
    YEAR ( LASTDATE ( '01 - Calendrier'[Date] ) ) * 10000
        + MONTH ( LASTDATE ( '01 - Calendrier'[Date] ) ) * 100
        + DAY ( LASTDATE ( '01 - Calendrier'[Date] ) )
VAR DateMin =
    YEAR ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) ) * 10000
        + MONTH ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) ) * 100
        + DAY ( DATEADD ( LASTDATE ( '01 - Calendrier'[Date] ); - Nbdays; DAY ) )
RETURN
    IF (
        HASONEVALUE ( buckets[value] );
        CALCULATE (
            COUNTX (
                SUMMARIZE (
                    CALCULATETABLE (
                        Microsoft;
                        ALL ( '01 - Calendrier' );
                        Microsoft[FK_IdCalendrier] >= DateMin
                            && Microsoft[FK_IdCalendrier] <= DateSelected
                    );
                    Microsoft[userName];
                    Microsoft[productName];
                    "Nb Cnx"; DISTINCTCOUNT ( Microsoft[FK_IdCalendrier] )
                );
                [Nb Cnx]
            );
            FILTER (
                SUMMARIZE (
                    CALCULATETABLE (
                        Microsoft;
                        ALL ( '01 - Calendrier' );
                        Microsoft[FK_IdCalendrier] > DateMin
                            && Microsoft[FK_IdCalendrier] <= DateSelected
                    );
                    Microsoft[userName];
                    Microsoft[productName];
                    "Nb Cnx"; DISTINCTCOUNT ( Microsoft[FK_IdCalendrier] )
                );
                [Nb Cnx] = VALUES ( buckets[value] )
            )
        );
        BLANK ()
    )

[v-yiruan-msft]

Hi @Cobra77 ,

It's glad to hear that the problem has been resolved. Thanks for sharing your formulas with us. Could you please just mark your last post as Answered? It will help the others in the community to find the solution easily if they face the same problem with you. Thank you.

Best Regards

Rena