Forum Discussion

EOW's avatar
EOW
Icon for Advocate II rankAdvocate II
1 year ago
Solved

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 tryin...
"DAX "
  • EOW's avatar
    EOW
    1 year ago

    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.

BITomS's avatar
BITomS
Icon for Solution Supplier rankSolution Supplier
1 year ago

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's avatar
EOW
Icon for Advocate II rankAdvocate II
1 year ago

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's avatar
    BITomS
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    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's avatar
      EOW
      Icon for Advocate II rankAdvocate II
      1 year ago

      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.