Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
EOW
Frequent Visitor

Maximum column height in visual, dynamic depending on drill down/expanding

I've got a visual that displays [Total Orders] for the last 10 weeks, summarised by a date hierarchy Year/Quarter/Month/Week (doesn't use actual calendar dates but our finaincial calendar). I'm trying to fetch the maximum displayed [Total Orders] to use in an axes calculation, dynamically updating with the displayed hierachy level. I've written the following measure:

 

var MaxInScope = SWITCH(
    TRUE()
    , HASONEVALUE(Dates[Week]     ), MAXX(ALLSELECTED(Dates[Week]     ), [Total Orders])
    , HASONEVALUE(Dates[Month]    ), MAXX(ALLSELECTED(Dates[Month]    ), [Total Orders])
    , HASONEVALUE(Dates[Quarter]  ), MAXX(ALLSELECTED(Dates[Quarter]  ), [Total Orders])
    , HASONEVALUE(Dates[Year]     ), MAXX(ALLSELECTED(Dates[Year]     ), [Total Orders])
)

return MaxInScope

 

but this isn't doing what i want (the blue bars should all be equal to 563)

EOW_0-1746529684253.png

 

Is there any way to extract the maximum value of a measure, dynamically taking into account the hierachry level thats displayed?

 

 

1 ACCEPTED SOLUTION
EOW
Frequent Visitor

@BITomS I tweaked your DAX and managed to get it working on the visual itself

SWITCH(TRUE()
, ISINSCOPE(Dates[Week]), MAXX(ALLSELECTED(Dates[Year], Dates[Quarter], Dates[Month], Dates[Week]), [Total Orders])
, ISINSCOPE(Dates[Month]), MAXX(ALLSELECTED(Dates[Year], Dates[Quarter], Dates[Month]), [Total Orders])
, ISINSCOPE(Dates[Quarter]), MAXX(ALLSELECTED(Dates[Year], Dates[Quarter]), [Total Orders])
, ISINSCOPE(Dates[Year]), MAXX(ALLSELECTED(Dates[Year]), [Total Orders])
, [Total Orders]
)

Unfortunetly, it seems like ISINSCOPE doesn't work in a Visual Title or Axes so I've hit a road block.
I've submitted it as an Idea on the Idea forum.
Let a visual title and Axes read displayed hierarc... - Microsoft Fabric Community
Hopefully it gets traction.

View solution in original post

4 REPLIES 4
BITomS
Continued Contributor
Continued Contributor

Hi @EOW ,

 

Unless I'm missing something, a simple measure of: Max([Total Orders]) should suffice. This would then apply dynamically based on whatever level you have selected within your slicers i.e. week, month etc.

 

A way I imagine this may not work for you is if you are not using a date dimension table in order to apply this slicer selection?

 

Alternatively, using Calculate(Max(Total Orders]),ALLEXCEPT('Dates')) might be what you are after. This then ignores the slicer selection on the date dimension.

EOW
Frequent Visitor

Hi @BITomS ,
[Total Orders] is a measure that is summing values from a related table, and thus wont work in a MAX() function. The date hierarchy is one of my own making, since my calendar is different to Power BI's inbuilt one.

BITomS
Continued Contributor
Continued Contributor

@EOW , perhaps this would work instead:

MaxInScope =
SWITCH(
TRUE(),
ISINSCOPE(Dates[Week]), CALCULATE(MAXX(VALUES(Dates[Week]), [Total Orders])),
ISINSCOPE(Dates[Month]), CALCULATE(MAXX(VALUES(Dates[Month]), [Total Orders])),
ISINSCOPE(Dates[Quarter]), CALCULATE(MAXX(VALUES(Dates[Quarter]), [Total Orders])),
ISINSCOPE(Dates[Year]), CALCULATE(MAXX(VALUES(Dates[Year]), [Total Orders]))
)

EOW
Frequent Visitor

@BITomS I tweaked your DAX and managed to get it working on the visual itself

SWITCH(TRUE()
, ISINSCOPE(Dates[Week]), MAXX(ALLSELECTED(Dates[Year], Dates[Quarter], Dates[Month], Dates[Week]), [Total Orders])
, ISINSCOPE(Dates[Month]), MAXX(ALLSELECTED(Dates[Year], Dates[Quarter], Dates[Month]), [Total Orders])
, ISINSCOPE(Dates[Quarter]), MAXX(ALLSELECTED(Dates[Year], Dates[Quarter]), [Total Orders])
, ISINSCOPE(Dates[Year]), MAXX(ALLSELECTED(Dates[Year]), [Total Orders])
, [Total Orders]
)

Unfortunetly, it seems like ISINSCOPE doesn't work in a Visual Title or Axes so I've hit a road block.
I've submitted it as an Idea on the Idea forum.
Let a visual title and Axes read displayed hierarc... - Microsoft Fabric Community
Hopefully it gets traction.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.