cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Calculate The max By year For all the user

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),
0,
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
1 ACCEPTED SOLUTION
Community Support

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.

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.

3 REPLIES 3
Community Support

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.

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.

Solution Sage

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

BBF

Regular Visitor
• I can't because it's some confidential data.