topic Re: Calculate The max By year For all the user in DAX Commands and Tips

Calculate The max By year For all the user

OmarSek75 — Wed, 05 Jun 2024 12:20:08 GMT

The first Step I calculate How many order has every user in the year.
Te idea here is if the user has one order or more in a month I will count as 1. Means if the user has order all the month , the resut will be 12.

This is the formula :

UniqueMonthsPerYear =

VAR NumberOccurencePerYear =

COUNTROWS (

SUMMARIZE (

'ft_qualityesod',

'DateTable'[MonthYear],

ft_qualityesod[user_email]

)

RETURN

IF (ISBLANK(NumberOccurencePerYear),

NumberOccurencePerYear)

Then I want to do some conditional formating , so I thought about creating a new measure where I calculate the max every year then divide the value on the max .... etc
Now I have problem calculating the max , this is my formula :

Number Declaration Conditional formating =

var MaxMonthsPerYear =

CALCULATE(

MAXX(

SUMMARIZE(

ALL('ft_qualityesod'),

'DateTable'[Year],

'ft_qualityesod'[user_email],

"UniqueMonths", [UniqueMonthsPerYear]

[UniqueMonths]

)

RETURN MaxMonthsPerYear
This is an image of result :

I don't understand why I have 12 all the time, for 2024 as we are in juin , I'am supposed to have 4 (AS my dara are until Avril).

I need help to resolve this problem.Thanks

Re: Calculate The max By year For all the user

BeaBF — Wed, 05 Jun 2024 14:28:54 GMT

OmarSek75 Hi! Can you paste the data on which you calculate these formulas?

BBF

Re: Calculate The max By year For all the user

OmarSek75 — Wed, 05 Jun 2024 14:37:26 GMT

I can't because it's some confidential data.

Re: Calculate The max By year For all the user

Anonymous — Thu, 06 Jun 2024 06:14:31 GMT

Hi, OmarSek75

Since you used ALL, all filters are cleared. This will take the maximum value in the UniqueMonths column of the summarize virtual table. If you want to keep the year filter, you should change your DAX to:

Number Declaration Conditional formating = var MaxMonthsPerYear = CALCULATE( MAXX( SUMMARIZE( ALLEXCEPT ('ft_qualityesod','DateTable'[Year]), 'DateTable'[Year], 'ft_qualityesod'[user_email], "UniqueMonths", [UniqueMonthsPerYear] ), [UniqueMonths] ) ) RETURN MaxMonthsPerYear

AllEXCEPT retains the year filter so that the maximum value for each year is correctly calculated.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.