How to get the Maximum Value for a Month from the Selected Months

[HassanAshas]

Hi, I am trying to label the "highest" value of the month from all the Months selected in my Table/Bar Chart. 

To do this, I wrote this DAX code, 

Overall Max Value Month = 
var _highest = 
    MAXX( 
    ALLSELECTED(
        Calender[Month]), 
        [TotalVisitors] 
    )

 Return 
 _highest 

As far as my understanding is, this code should work and should return the highest value from all the selected months. But for some reason, it is not working. 

and to my surprise, this same code does work if I apply it to Year or to Quarter. For example, following code works for me, 

Overall Max Value Quarter = 
var _highest = 
    MAXX( 
    ALLSELECTED(
        Calender[Quarter]), 
        [TotalVisitors] 
    )

 Return 
 _highest 

But the same thing doesn't work for Month or for Month-Year. 

As an example, please check the below results I am getting with these Measures, 

Can anyone help me out in figuring what is the problem here?

If you would like to download the Sample Power BI File, you may do so from here: https://drive.google.com/file/d/13WsVDSb-2zD1WJIedP-Bx9Rzh6tOolW3/view?usp=sharing

[OwenAuger · Accepted solution]

Hi @HassanAshas 

The unexpected results are due to "sort by" columns defined for certain columns, in this case Month and Month Year.

When a sort by column is defined for a particular column, it is automatically included as a group-by column within the DAX query generated by Power BI visuals.

For example, in your visual grouped by 'Calender'[Year Month], the DAX query includes both 'Calender'[Year Month] and 'Calender'[Year Month Number].

Because of this, I would recommend this pattern as the safest way to write these measures:

Overall Max Value Month = 
CALCULATE (
        MAXX ( 
            VALUES ( Calender[Month] ),
            [TotalVisitors]
        ),
        ALLSELECTED ( Calender )
    )

Overall Max Value Quarter = 
CALCULATE (
        MAXX ( 
            VALUES ( Calender[Quarter] ),
            [TotalVisitors]
        ),
        ALLSELECTED ( Calender )
    )

// etc.

In this pattern, ALLSELECTED ( Calender ) ensures that the "overall" filter context for all columns of the Calender table is restored, which will include the sort-by columns.

Regards

[HassanAshas]

Thank you so much for the clarification! This worked like a charm!