What-if Parameter With Variable End Value

[Pablinho]

Hi!

So, I created a New Parameter called "Parameter". It uses a GENERATESERIES(0,73,1). This allows me to have a slice that goes from 0 to 73 in increments of 1. However, I want to define the end value (73) as a variable from a table, let say X. So for example, if X =sum('table'[Column]), GENERATESERIES(0,X,1). It doesn't work. It makes sense that it does not work because there isn't a relationship between those tables, but I can't find a way to create one. Is there a workaround for this?

Thank you!

[AlexisOlson]

This works as a calculated table for me:

Parameter = 
VAR X = SUM ( 'table'[Column] )
RETURN
    GENERATESERIES (0, X, 1 )

However, the X variable (and hence the Parameter table) will not be affected by any slicers or filters. Calculated tables cannot be responsive to filters (unless they are just temporary tables used within a measure) since they are only calculated once per time the data is loaded or refreshed.

[Pablinho]

I see now what my problem was. I have a date filter for the complete dashboard, so it is making the sum of all the dates instead for the one applied. Do you think there is a workaround for that?

Thank you so much for the response! It was very insightfull!

[AlexisOlson]

Since the parameter is a calculated table, it cannot be responsive to your date filters.

There may be another way to ultimately achieve your actual end goal that you're creating this parameter for, but I don't have enough context to make any specific suggestions.

[Pablinho]

I thought I could find a workaround but nothing so far. What I have is a lot of appended queries. Each table has a date column so I have a big appended table with almost the same values repeating but with a set of diferent dates. For example:

ID1 DATE1 ETC

ID2 DATE1 ETC

ID3 DATE1 ETC

ID4 DATE1 ETC

ID1 DATE2 ETC

ID2 DATE2 ETC

ID3 DATE2 ETC

ID4 DATE2 ETC

I have a slicer to filter by date so that only the information for that day is presented. I also have a gauge that shows a percentage and it changes based on the values that each date has. With a What-if parameter I am able to add a slicer that modifies that gauge with a pre-set value. However I have that daily value. What I want to do is to automatically plug that value to the parameter, so that it changes based on the date.

Right now the parameter returns the sum of all the dates because the filter is not being applied.

I hope that gives more context. Thanks!

[AlexisOlson]

I'm not sure I fully understand the purpose of the parameter. Generally, a parameter is used to give control of some variable to the end user.

You can use measures to define min/max/target on a gauge visual and measures are responsive to filters. Is this sufficient or does the user need to control something manually (rather than relying on a calculation to do the work)?

[Pablinho]

The user needs to control something manually so that they can do a What-If? Analysis. By default the gauge is at a current value for the day (20%). Then the idea is that the user can use a slicer or anything to do a proyection, let's say increase 5%. However that slicer max value is defined each day. Right now I need to manually see what the value is an plug it into the parameter, but want to do it automatically.

[AlexisOlson]

One workaround would be to set the parameter so that its maximum is the maximum possible over each day individually so you know you have all options covered. If you wanted, you could also set a measure filter on the parameter slicer so that only the values up to the max for the currently selected day show up. From the user's perspective, this should work pretty much the same as the dynamic parameter you want.

[Pablinho]

Great suggestions!

However, the idea is that the user cannot go beyond the current posible goal. Ffor example, todays goal max is 73 but tomorrow max is 71 and I set it to 73, then the user could place 73 and think that it is achivable, when is not. That is why I need it to change dynamically.

Do you think you could elaborate on the second one? I think that is what I been trying to do but haven't succeed with.

Thank you again!

[AlexisOlson]

The Parameter might look something like this to capture the maximal value for any date.

Parameter =
VAR X =
    MAXX ( Dim_Date, CALCULATE ( SUM ( 'table'[Column] ) ) )
RETURN
    GENERATESERIES ( 0, X, 1 )

Then you could write a measure like

SlicerFilter = IF ( MAX ( Parameter[Parameter] ) < SUM ('table'[Column] ), 1, 0 )

 and use this measure as a filter on your slicer visual:

I'm attaching a simplified example.

[Pablinho]

I see! Thank you so much. It does not work thought.

If I create a slicer with the Parameter as it only field, it always goes from 0 to 15. It doesn't matter wich date is being selected. The filter is working in the opossite direction. The Parameter filters the dates, but I want exactly the opossite, the dates filtering the Parameter.

I am thinking of using the FILTER function but need to read a bit about it.

Thank you!!

[AlexisOlson]

If you select different dates in the example I shared, then you do get different ranges showing. This isn't what you wanted?

[Pablinho]

Yes, that is what I want but the provided example didn't work. The slicer always shows 15, no matter the date:

[AlexisOlson]

It does exactly what I described using a list or dropdown slicer. It appears that the range version of the slicer is indifferent to the measure filter though.

[Pablinho]

You are right! Wow... then the slicers are the ones that doesn't allow it? That's super sad.

Thank you so much for all the help!