DAX - How to calculate maximum of daily unique count within current month using measure

[ltang6]

Hi all,

I want to use DAX to calculate a measure based on Powerbi Dataset. Since i need to publish it, i cannot include any calculated columns or tables.

The objective is to calculate the maximum unique user utilization in the current month.

The methodology is:

1. Restrict to latest month using 'Month' column, and to correct tenant. I only want 'test1'.

2. Count the unique number of user for each date -> daily utilization.

3. Return the maximum daily utilization of this latest month.

The result of this measure will be displayed in a 'Card' visualization.

I would also want to apply extra filter from Visualization filter to restrict user department.

For the data listed below, by applying the department filter to be only 'Group 1', i would expect the correct output to be 3.

'Group 1' cannot be hardcoded like 'test1' because it is subject to change.

My sample data are like this:

Table 1 contains a list of user activity.

User	Date	Month	Tenant
a@gmail.com	1-Aug-23	1-Aug-23	test1
b@gmail.com	1-Aug-23	1-Aug-23	test1
a@gmail.com	1-Aug-23	1-Aug-23	test1
c@gmail.com	1-Aug-23	1-Aug-23	test1
d@gmail.com	1-Aug-23	1-Aug-23	test1
c@gmail.com	2-Aug-23	1-Aug-23	test1
d@gmail.com	2-Aug-23	1-Aug-23	test2

Table 2 contains the details information on each user.

User	Department
a@gmail.com	Group 1
b@gmail.com	Group 1
c@gmail.com	Group 2
d@gmail.com	Group 1

The relationship between the two tables are already build by link the two User columns.

[davidwyt · Accepted solution]

below is how to calculate max distinct count of users, and you can add filters to set current month and tenant

Calculate(

    MaxX(

        Values('Table1'[Date]),

        Calculate(

             DISTINCTCOUNT ('Table1'[user])

        )

)

[ltang6]

Yes simplicity is best, it is working! i keep thinking about the outside filter will not be able to apply, but IT WORKS!

[ltang6]

Hello Binbin,

Thank you so much for your reply.

I think what you suggest is exactly what i need. It's only that the actual database is quite large.

Up on trying the above DAX, i have got the error that the query size is too large.

Not sure if there's a way to solve this.

[Anonymous]

Hi @ltang6 ,

Please try to create a measure with below dax formula:

Measure =
VAR cur_dpt =
    SELECTEDVALUE ( 'Table 2'[Department] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table 2'[User] ),
        FILTER ( ALL ( 'Table 2' ), [Department] = cur_dpt )
    )
VAR tmp1 =
    FILTER ( ALL ( 'Table 1' ), [User] IN tmp )
VAR tmp2 =
    SUMMARIZE ( tmp1, [Month], "Cnt", DISTINCTCOUNT ( 'Table 1'[User] ) )
RETURN
    SUMX ( tmp2, [Cnt] )

Add a card visual with measure:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.