Skip to main content
cancel
Showing results for 
Search instead 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

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
Solution Sage
Solution Sage

@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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.