cancel
Showing results 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

Frequent Visitor

## Highest value in measure with time binning

Hello,

I have a measure UL that totals the number of unit loads for a given time bin (daily, weekly, monthly) - see below.  The total value of all bars is 3156 and is displayed on a card.

As you can see from the chart, the highest bar value of UL is 812, but I don't know how to retrieve 812 using the measure.

When I select the specific bar the measure does change to the correct amount, which tells me that I could maybe filter it somehow.

Is there anyone who knows how to fix this?  Much obliged.

5 REPLIES 5
New Member

Hello,

I tried doing the measure that @mickey64 suggested but I think that I don't fully understand the syntax.  I have attached a link https://drive.google.com/open?id=1_G6o9EW-mGjNZ9KoxwQfF9WFuewigadT&usp=drive_fs  here.   The data has been anonymized so the totals are a bit different but the concept is the same.

As you can see, the card shows the total value of the measure UL, but what I am looking for is the size of the largest column (in the case of monthly it would be 1510, but weekly it would 876 and daily it would be 550.

I appreciate all the help!

Regards

Julian

Super User

I was able to create Daily and Monthly measures, but I was unable to create a measure that switches with a slicer, as I don't understand the "Date-Bin" functionality you are using.

M_Maxx-Daily = MAXX(SUMMARIZE('data1','data1'[Daily],"M-UL",'data1'[UL]),'data1'[UL])

M_Maxx-Weekly = MAXX(SUMMARIZE('data1','data1'[Weekly],"M-UL",'data1'[UL]),'data1'[UL])

M_Maxx-Monthly = MAXX(SUMMARIZE('data1','data1'[Monthly],"M-UL",'data1'[UL]),'data1'[UL])

Frequent Visitor

Hello,

Thanks @mickey64 that was very helpful.

With some applied research, I was able to put all three options into a SWITCH function to get the desired output, but it took some effort.

First, I was not able to directly access the selected value for the time bin, so a workaround is to add  DAX columns 'Bin' and 'Bin Field' that essentially copies the composite value and makes them retrievable by the SELECTEDVALUE function,

One that was done, I was able to create two measures [Selected Value] and [Selected Field] that retrieves the current bin and field name.

Finally, I was then able to use @mickey64 's function wrapped inside a SWITCH for the three types of bins available, using [Selected Value].

Now the highest value measure is displayed on the card and interacts dynamically with the bin slicer:

This solves the immediate issue, but I wanted to see whether I could pass the [Selected Field] measure displayed on the card into the second argument of the SUMMARIZE function to eliminate the SWITCH and make things dynamic.

I tried a bunch of different ways to make a measure [Highest Value no Switch] using the [Selected Field] in place of the second argument of @mickey64's function but it didn't work.  Evidently there isn't a DAX equivalent of the Excel INDIRECT function that would work here.  It appears that it is looking for a column name and can't accept a measure.

Any ideas on how I can overcome this?

Thanks

Julian

Super User

Plese try this measure below.

M_Max = MAXX(SUMMARIZE('Table','Table'[Date],"MeasureValue",[Measure]),'DATA'[Measure])

Super User

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

Regards,
Rita Fainshtein | Microsoft MVP

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.