Slider problems

[RichardLinderma]

I have the following calculated DAX table, used on a slider:

Future Travel Slider =
VAR MaxValue = MAX('Future_Predicted'[Number_Of_Days_Slider])
RETURN
ADDCOLUMNS(
GENERATESERIES(0, MaxValue, 1),
"Label", "Value " & [Value]
)

There is a slicer called code[account_code] that is supposed to filter the slider to how many days are on it. For example, if "company1" was selected on code[account_code], then Number_Of_Days_Slider would equal 70, thus Future Travel Slider should only go up to 70 too. However, unlike the other visuals on the page, the slider isn't affected by the slicer despite there being a relationship between 'Future_Predicted' and 'code'. How do I fix this so that the slider only goes up to the Number_Of_Days_Away based on the code[account_code] selected?

[danextian]

Hi @RichardLinderma 

Calculated tables and columns are not aware of slicer selections, so even though you can reference a measure inside them, its value won't change based on slicer input. In your case, the value of the MaxValue variable will always return the actual max of 'Future_Predicted'[Number_Of_Days_Slider], regardless of what is selected in the slicer. The calculated table must already contain all possible rows for every potential selection. For example, if account code 001 has a max value of 70, the table should include a column identifying that account and another column with rows from 1 to 70.

Sample calc table

AccountDayTable = 
VAR BaseTable =
    DATATABLE (
        "AccountCode", STRING,
        "MaxDay", INTEGER,
        {
            { "001", 70 },
            { "002", 50 }
        }
    )
VAR _Expanded =
    GENERATE (
        BaseTable,
        ADDCOLUMNS ( GENERATESERIES ( 1, [MaxDay], 1 ), "Day", [Value] )
    )
RETURN
    SELECTCOLUMNS ( _Expanded, "Account code", [AccountCode], "Day", [Day] )

[RichardLinderma]

This is very interesting. Would you be able to adjust my code based on the AccountDayTable you provided, please?

[RichardLinderma]

Another thing to mention is that Number_Of_Days_Slicer is a column rather than a measure.

[v-saisrao-msft]

Hi @RichardLinderma,

Thank you for reaching out to the Microsoft Fabric Forum Community.

 Please check teh updated dax below:

AccountDayTable = 
VAR BaseTable =
    SUMMARIZE (
        'Future_Predicted',
        'Future_Predicted'[AccountCode],
        "MaxDay", MAX('Future_Predicted'[Number_Of_Days_Slider])
    )
VAR _Expanded =
    GENERATE (
        BaseTable,
        ADDCOLUMNS (
            GENERATESERIES (1, [MaxDay], 1),
            "Day", [Value]
        )
    )
RETURN
    SELECTCOLUMNS (
        _Expanded,
        "Account code", [AccountCode],
        "Day", [Day]
    )

Thank you.

[RichardLinderma]

Thanks. Just need to point out that [account_code] is part of the table 'code' rather than 'Future_Predicted'.

[v-saisrao-msft]

Hi @RichardLinderma,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

[v-saisrao-msft]

Hi @RichardLinderma,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @RichardLinderma,

I have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @RichardLinderma,

Please check the below pbix file by using sample data by switching to dropdown in the visual.

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

Thank you.

[Royel]

  Hi @RichardLinderma, I tried to iterate on your problems in Power BI and come up with a solution.

Calculated Table 

Future Travel Slider =

VAR MaxPossibleValue =

    CALCULATE(

        MAX('Future_Predicted'[Number_Of_Days_Slider]),

        ALL('Future_Predicted')

    )

RETURN

    ADDCOLUMNS(

        GENERATESERIES(0, MaxPossibleValue, 1),

        "Label", "Value " & [Value]

    )

Measure for Cards 

Maximum days =

VAR SelectedMaxDays =

    IF(

        HASONEVALUE('code'[account_code]),

        CALCULATE(

            MAX('Future_Predicted'[Number_Of_Days_Slider]),

            USERELATIONSHIP('code'[account_code], 'Future_Predicted'[account_code])

        ),

        CALCULATE(

            MAX('Future_Predicted'[Number_Of_Days_Slider]),

            ALL('Future_Predicted')

        )

    )

RETURN

    IF(ISBLANK(SelectedMaxDays), 0, SelectedMaxDays)

And as you can see, when i select maximum days is showing 70 and there is no reflection on the Future Travel Slider 

Download the Sample Solution File: https://getshared.com/BG3FoAHd 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[rohit1991]

Hi @RichardLinderma 

Power BI slicers don’t dynamically adjust their range based on another slicer’s selection when the values come from a calculated table like your Future Travel Slider. Calculated tables are static at refresh time and don't respond to slicer selections.

Workaround:

• Move the slider logic into a measure or a calculated column that reflects the selected account_code, or

• Use a What-If parameter instead of a calculated table, but be aware the range won’t dynamically shrink/grow.

• If you need the slider to adjust dynamically, you’ll need to use a field from your data model that’s directly affected by filters (not a calculated table). Consider filtering visuals with a normal slicer, not a dynamically-generated one.

[RichardLinderma]

Hi rohit1991,

Thank you for your message. Would you be able to give me the DAX functions for this?