Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
OmarSek75
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)
image.png
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 : 
image (1).png

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
v-jianpeng-msft
Community Support
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.

 

 

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.

View solution in original post

3 REPLIES 3
v-jianpeng-msft
Community Support
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.

 

 

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.

BeaBF
Super User
Super User

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

 

BBF

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.