topic Re: DAX - How to calculate maximum of daily unique count within current month using measure in DAX Commands and Tips

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

ltang6 — Thu, 03 Aug 2023 09:30:24 GMT

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.

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

Anonymous — Mon, 07 Aug 2023 02:55:31 GMT

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.

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

ltang6 — Wed, 09 Aug 2023 05:44:18 GMT

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.

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

davidwyt — Wed, 09 Aug 2023 06:31:29 GMT

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

)

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

ltang6 — Thu, 10 Aug 2023 06:11:06 GMT

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