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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
daweedle
Regular Visitor

Maximum of measure aggregated dynamically at different date hierarchy levels

Hi community, I have an interesting problem that I'm not able to find a solution for anywhere here:

 

  • I have a measure calculating the sum of a value, let's say [measure] = SUM([value])
  • I am putting this measure on the y-axis of a bar chart, where the x-axis is the date hierarchy for a date field, [date]
  • Users can drill up or expand down the x-axis date hierarchy, e.g. from quarter-level to year-level
  • I want to create a measure (or is it a column?) that will return the maximum of [measure] as displayed in the visual, meaning that it needs to be dynamic based off the date hierarchy level specified in the visual

 

Here is the bar chart below, with [measure] on the secondary-axis as a black line:

 

daweedle_0-1686070162652.png

 

In the above, the date hierarchy level is quarter, and at this specified level, the max of [measure] is 2, therefore I want my desired measure to return 2. In the below however, the date level is year, and here I want my desired measure to return 4.

 

daweedle_1-1686070249373.png

 

Note also that my desired measure also needs to be dynamic based off of slicer filtering - in this case, for language.

 

Is this possible?

2 REPLIES 2
amitchandak
Super User
Super User

@daweedle , Based on what I got

 

Switch( True() ,

isinscope(Date[Date]) , [M1],

isinscope(Date[Month]) , [M2],

isinscope(Date[Qtr]) , [M3],

[M4]

)

This does not work. My desired measure needs to return the maximum at the grand total level, i.e. the maximum value of [measure] for the whole data set (with filtering).

 

The challenge is that I require a 2-step calculation. The 1st calculation is for [measure], which is calculated at different levels as per user-selected hierarchy level. The 2nd calculation is performed at the grand total level, to get the max value of [measure] across all values returned from the 1st calculation. Using ISINSCOPE will return a blank for the 2nd calculation since it will return FALSE at the 2nd calculation.

 

Another way of thinking about this problem - I basically want the following expression:

 

MAXX(SUMMARIZE(Table, [DateLevel], "M", [measure]), [M])

 

where [DateLevel] is dynamic and specified by the date level of the visual. ISINSCOPE does allow me to check the date level but only for calculations at that same level - and unfortunately not for calculating at a higher level to get the aggregate maximum.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors