Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
@besomebody20 , Thank you so much for this! Works perfectly! I was pulling my hair out the whole day over this!
Hello can you please try:
MAXX(ALL(Dim_calendar), [ratio])
dim date:
created sample of your data:
result:
Please try
=
MAXX (
CALCULATETABLE (
VALUES ( dim_calendar[Activity Mo Name] ),
REMOVEFILTERS ( dim_calendar )
),
[Ratio]
)
I'm sure we don't have to complete picture. Please try
=
MAXX ( KEEPFILTERS ( ALL ( dim_calendar[Activity Mo Name] ) ), [Ratio] )
@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?
Perhaps you are using the wrong column in the visual. Perhaps other columns are also present in the same visual
@tamerj1 Checked all the above. Only dim_calendar{Activity Mo Name], [Ratio] and [Max} in the table...
@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])
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:
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |