Forum Discussion
Helper IVCan't get to the right MAX
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?
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.
13 Replies
- besomebody20
Resolver I
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.
PshemekFLKbesomebody20 , Thank you so much for this! Works perfectly! I was pulling my hair out the whole day over this!
- Greg_Deckler
Community Champion
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
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
- tamerj1
Hi PshemekFLK
what result are getting? Are you filtering a certain year?
- tamerj1
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
I'm sure we don't have to complete picture. Please try
= MAXX ( KEEPFILTERS ( ALL ( dim_calendar[Activity Mo Name] ) ), [Ratio] )
