Can't get to the right MAX

[PshemekFLK]

I have this table:

And would like to achieve a simple MAX column:

I'm trying this measure but it doesn't work:

MAXX(ALL(dim_calendar[Activity Mo Name]), [Ratio])

Can you guide me towards the right solution?

[besomebody20 · Accepted solution]

Please try this measure:

CALCULATE(
    MAXX(
        ADDCOLUMNS(
            SUMMARIZE(
                'dim_calendar',
                'dim_calendar'[Activity Mo Name]
            ),
            "_ratio", [Ratio]
        ),
        [_ratio]
    ),
    ALLSELECTED('dim_calendar')
)

 The ADDCOLUMNS and SUMMARIZE functions create the table for MAXX to iterate over to find the max ratio. The ALLSELECTED function will clear the filter context on the row of the table visual to give you the max ratio of all rows.

[PshemekFLK]

@besomebody20 , Thank you so much for this! Works perfectly! I was pulling my hair out the whole day over this!

[Anonymous]

Hello can you please try:

MAXX(ALL(Dim_calendar), [ratio])

dim date:
 

created sample of your data: 

 

result:
 

 

[tamerj1]

@PshemekFLK 

Please try

=
MAXX (
    CALCULATETABLE (
        VALUES ( dim_calendar[Activity Mo Name] ),
        REMOVEFILTERS ( dim_calendar )
    ),
    [Ratio]
)

[PshemekFLK]

@tamerj1  Also didn't work. Exact same result as last time... 

[tamerj1]

@PshemekFLK 

I'm sure we don't have to complete picture. Please try

=
MAXX ( KEEPFILTERS ( ALL ( dim_calendar[Activity Mo Name] ) ), [Ratio] )

[PshemekFLK]

@tamerj1  again same result...

I also thought there must be something else blocking but just checked for a thrid time:

no filters applied to the table

[Ratio] = DIVIDE([Measure 1],[Measure 2])

[Measure 1] = DIVIDE(SUM(fct_bookings[Column 1]),1000)

[Measure 2] = DIVIDE(SUM(fct_bookings[Column 2]),1000)

dim_calendar is properly connected to fct_bookings with "one to many" relation

What else can be stopping the MAXX measure from working properly?

[tamerj1]

@PshemekFLK 

Perhaps you are using the wrong column in the visual. Perhaps other columns are also present in the same visual 

[PshemekFLK]

@tamerj1 Checked all the above. Only dim_calendar{Activity Mo Name], [Ratio] and [Max} in the table...

[tamerj1]

Hi @PshemekFLK 

what result are getting? Are you filtering a certain year?

[Greg_Deckler]

@PshemekFLK If Ratio is a measure, maybe: 

Max Measure =
  VAR __Table = ADDCOLUMNS(DISTINCT(ALL(dim_calendar[Activity Mo Name])),"__Ratio",[Ratio])
RETURN
  MAXX(__Table, [__Ratio])

[PshemekFLK]

@Greg_Deckler

Unfortunately doesn't work. I'm getting same result with yours and mine formula which is just replication of Ratio column. It's like I can't remove the filter from "Activity Mo Name" no matter what I try.

@tamerj1 , @Greg_Deckler   the result:

[PshemekFLK]

@tamerj1 I'm not filtering on a year, no filters on that table

[Ratio] measure is a simple divide or SUMX over fact tables. I really don't get why it doesn't work