topic Re: Help declaring max value measure for a guage? in DAX Commands and Tips

Help declaring max value measure for a guage?

shaun-bi — Wed, 27 Oct 2021 11:52:26 GMT

I have a single dataset that has table called "reservations", which is presented in a BI report as a simple table. In this table it has a column called "buildings". Each building has a maximum capacity number (not contained in the table) which is hard set and can be included in the measure (if that makes sense to).

I want a measure to count the rows for each building in the table i.e London so that when a slice is set in the report to filter the table by building, and to define a maximum value for the guage which is building specific.

We're just using a simple measure to count the rows displayed in the table today which is applied to the guage:

Measure1 = COUNTROWS(reservations)

Re: Help declaring max value measure for a guage?

Greg_Deckler — Wed, 27 Oct 2021 12:00:34 GMT

shaun-bi Maybe:

Max Measure = VAR __Building = MAX('Table'[Building]) RETURN SWITCH(__Building, "Building1",44, "Building2",33, ... )

Re: Help declaring max value measure for a guage?

Fowmy — Wed, 27 Oct 2021 12:17:02 GMT

shaun-bi

Do you need a new measure to get the capacity number based on the currently filtered building ? How about a new measure?

MaxValue = SELECTEDVALUE (reservations[capacity number]) , 0 )

MaxValue = MAX(reservations[capacity number])

Re: Help declaring max value measure for a guage?

shaun-bi — Wed, 27 Oct 2021 12:40:51 GMT

Thanks, this sets the maximums perfectly.

How do I combine that with the countrows to compare the table data to the maximum value. As right now, the gauge is just displaying the maximum values.

I've used BI for 2 hours only so sorry for the newbie questions!

Re: Help declaring max value measure for a guage?

v-robertq-msft — Mon, 01 Nov 2021 08:32:43 GMT

Hi,

According to your description, I can clearly understand your requirement, you want to create a gauge chart to display the comparison between your maximum capacity number and current capacity number, right? I think you can achieve your requirement by creating some measures like this:

This is the test data I created based on your description:

maximum capacity number = MAX('reservations'[capacity number])

Current capacity numbers = COUNT(reservations[buildings])

Max = maxX(ALL(reservations),[capacity number])

Min = 0

And you can create a gauge chart and place it like this, then you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.