Dynamic Filter for Slicer

DAX DAX Calculations
1

Hi all, I’m trying to set the max value of a slicer based on the max value of a measure.

What you will find in the attached pbi (bottom), is a simple model where services have a total capacity available and contracts related to these services are consuming this capacity over time. As you will see from the matrix, this works perfectly fine. However, I would also like to be able to filter on the availability using a slicer. The min value of the slicer should always be zero, but the maximum would need to be the max value of "Monthly availability) which you see in the matrix. Somehow, I’m not able to get this to work.

Also … either I find a way to set the max value of generate series (of the slicers) dynamically, or I apply a filter to the visual with 0 & 1 for each value which falls in the range of the monthly availabily. I can’t get neither to work :slight_smile:

Model:

image
image544×636 6.53 KB

The report:

image
image1266×563 36 KB

So this max value of the slicer should be the max availability depending on the slicers set on date, location & type.

Hope this makes sense.

Thanks.Edna - Dynamic Max Value Slicers.pbix (83.8 KB)

Christopher

2

Quick update: so basically I would need to have max of “availability” within the current filter context.

From this value, I could then do a filter on the “Service Availability Slicer” like:

Availability Slicer Filter =
VAR MaxAvailabilityValue = ???
VAR CurrentSelection = SELECTEDVALUE(‘Service Availability Slicer’[Value])
RETURN
IF(CurrentSelection <= MaxAvailabilityValue,
1,
0
)

3

Hi @Wlknsn

Do check this video. This is almost similar to your requirement. Let me know if this is helpful or you still face any issues.

Thanks
Ankit J

4

Hello Ankit,

Many thanks for this. This is exactly where I got the idea from, however, I cannot seem to get the second part to work so that range is automatically set based on the “availability” range of matrix. His summarize goes over one simple table, mine has a few dependencies and a calculatetable in the middle, so I’m seeking help to get this in place. I can’t even get the max of all selected services in that range … so really doing something wrong (however, if I test the formula in a simple card visual, it does return the max of selected values, so really lost here).

5

Hi @Wlknsn - Below is not the complete solution however I did try to Replicate your measure and get maximum value for the Availability.Please check below Measure

> Availability =
> VAR DateTab =
>     SUMMARIZE (
>         'Date',
>         'Date'[YearMonthnumber],
>         "__MinDate", MIN ( 'Date'[Date] ),
>         "__MaxDate", MAX ( 'Date'[Date] )
>     )
> VAR Calc =
>     ADDCOLUMNS (
>         ADDCOLUMNS (
>             GENERATE ( DateTab, Services ),
>             "__occu",
>                 SUMX (
>                     FILTER (
>                         Contracts,
>                         Contracts[Service] = Services[Name]
>                             && Contracts[Start] <= [__MaxDate]
>                             && Contracts[End] >= [__MinDate]
>                     ),
>                     Contracts[Quantity]
>                 )
>         ),
>         "Avail", Services[Capacity] - [__occu]
>     )
> RETURN
>     MAXX (
>         GROUPBY (
>             Calc,
>             'Date'[YearMonthnumber],
>             Services[Location],
>             "Availab", SUMX ( CURRENTGROUP (), [Avail] )
>         ),
>         [Availab]
>     )

Let me know if above is helpful to get the Maximum value or any other help is needed. Edna - Dynamic Max Value Slicers.pbix (90.5 KB)

PS: Naming Conventions and Formatting, please correct yourself.

Thanks
Ankit J

1 Like
6

Hi @Wlknsn, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

7

Hi @Wlknsn, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!