DAX: Sum of Max of Count of Multiple Rows at User/Activity Level in Selected Time period

[SmileChoc]

Hi

I am looking to get the help with Sum of Max of Count of Multiple Rows at User/Activity Level in Selected Time period

User conducts activity on same/different day. 

Activity D is a combination of activity A, B and C meaning is is counting as 1 towards activity A, B and C

My raw data are as follows

Date	User	Activity
3/03/2025	3	A
4/03/2025	3	A
11/04/2025	2	C
24/04/2025	2	B
28/04/2025	2	C
2/05/2025	2	A
19/05/2025	2	C
22/05/2025	2	C
29/05/2025	2	C
2/06/2025	2	A
10/06/2025	1	C
17/06/2025	1	B
19/06/2025	3	A
24/06/2025	1	C
25/07/2025	1	B
25/07/2025	1	C
6/08/2025	3	A
6/08/2025	3	B
6/08/2025	3	C
12/08/2025	1	B
29/09/2025	1	B
27/10/2025	1	A
1/11/2025	1	D
17/11/2025	1	B

With dynamic period search I am expecting the following result:

Count A, Count B, Count C = distinct count of activity (including D) per user.

Max is a Max value of Count A, Count B, Count C

Sum is a sum of individual activities or of Max column

For time period of 01/03/2025 - 30/11/2025 

User	Count B	Count C	Count A	Max
1	6	4	2	6
2	1	5	2	5
3	1	1	4	4
SUM	8	10	8	15

For time period of 01/03/2025 - 31/07/2025

User	Count B	Count C	Count A	Max
1	2	3	0	3
2	1	5	2	5
3	0	0	3	3
SUM	3	8	5	11

The calculation is required in DAX as user should be able to select any time period with the correct individual Activity Count per User and moveover for Sum of MAX.

Thank you fo ryour help in advance

[v-sshirivolu]

Hi @SmileChoc ,

I would take a moment to thank @FBergamaschi  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[v-sshirivolu]

Hi @SmileChoc ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[mizan2390 · Accepted solution]

hi @SmileChoc ,

If I understand correctly, you want to calculate----
1. To count the occurrences of activities A, B, and C, but treat Activity 'D' to counts simultaneously towards A, B, and C. This means a single row of data (one occurrence of D) must be evaluated multiple times and counted towards the totals of A, B, and C.

2. To to evaluate their total count for A, B, and C, and dynamically find the highest value among those three specific numbers for each user. 

3. To compute the max for each individual user first, and then sum those individual maximums together.
Use this DAX to Count A,B and C. 

Count A = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    'Table'[Activity] IN { "A", "D" }
) + 0

Count B = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    'Table'[Activity] IN { "B", "D" }
) + 0

Count C = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    'Table'[Activity] IN { "C", "D" }
) + 0

Now to calculte the Max, 

Max Count =
SUMX (
    VALUES ( 'Sheet2'[User] ), 
    VAR _countA = CALCULATE ( COUNTROWS ( 'Sheet2' ), 'Sheet2'[Activity] IN { "A", "D" } ) + 0
    VAR _countB = CALCULATE ( COUNTROWS ( 'Sheet2' ), 'Sheet2'[Activity] IN { "B", "D" } ) + 0
    VAR _countC = CALCULATE ( COUNTROWS ( 'Sheet2' ), 'Sheet2'[Activity] IN { "C", "D" } ) + 0
    RETURN
        MAX ( _countA, MAX ( _countB, _countC ) )
)

If this solved your problem, please give me Kudos and mark this as sloved.

[FBergamaschi]

Hi @mizan2390,

I think you were able better than me to understand what @SmileChoc  was asking for. Thanks a lot

Your answer looks good but I strongly suggest to:

1 - avoid using the +0 at the end of measures. I know it is nice to see 0 (and that's what @SmileChoc noted in his request but it is very dangerous to do this for performance reasons)

2 - avoid restating the code of the measures Count A etc in Max Count, otherwise maintenance will be terribly costly

So my solution is attached, in which I avoid those things. Hats off to you for having understood the requirement.

Count A =

CALCULATE(

    COUNTROWS( 'Fact' ),

    Acticity[Activity] IN { "A", "D" }

)

Count B =

CALCULATE(

    COUNTROWS( 'Fact' ),

    Acticity[Activity] IN { "B", "D" }

)

Count C =

CALCULATE(

    COUNTROWS( 'Fact' ),

    Acticity[Activity] IN { "C", "D" }

)

Max Count =

SUMX (

    VALUES ( User[User] ),

    MAX ( [Count A], MAX ( [Count B], [Count C] ) )

)

File attached, thanks again @mizan2390 

@SmileChoc if this helped I think it is a solution shared in two (or mine is just optimization, how you prefer!)

[mizan2390]

@FBergamaschi thank you for the feedback. Yes, it was difficult to understand the requirement. Took few moments to understand.
theres no question with both of your suggestion. As you said, number 1 was requested. And for the second point, I just showed the full breakdown of the measure. But i would have used your suggested measure. Thanks again. 

[FBergamaschi]

Hi @SmileChoc 

while trying to solve I found immediately an issue

You say

With dynamic period search I am expecting the following result:

Count A, Count B, Count C = distinct count of activity (including D) per user.

But your result shows

For time period of 01/03/2025 - 30/11/2025 

User	Count B	Count C	Count A	Max
1	6	4	2	6
2	1	5	2	5
3	1	1	4	4
SUM	8	10	8	15

1 - Question: how can the distinctcount return 6 for User 1, when acticities are just 4 (A,B,C,D)?

2 - Considering maybe you meant count the rows I have the following results that still do not match your expectations

Please can you clarify why you expect 6 for user 1 for Count B (and what you mean by Count B)?

Thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI