PowerBI dynamic columns based on slicer

[abiyevnijat]

Hello everyone,

Business Time % =

IF ( 'Dashboard'[Business] < [SLA Time], 1, [SLA Time] / 'Dashboard'[Business] )

I have calculated column in my main table (Dashboard). My SLA Time measure is SLA Time = 2 which is not dynamic. I want to create slicer dropdown visual from 1 to 48 instead of SLA Time. When I select my value from slicer I want to get result in Business Time % dynamically. How can I get my result? My Business column in my table in Decimal number format and it shows hours.

[abiyevnijat]

Business Time % =

IF ( 'Dashboard'[Business] < [SLA Time], 1, [SLA Time] / 'Dashboard'[Business] )

with this formula I can get the answer. But I have SLA Time = 2 measure and I change this manually. I want to add slicer (dropdown) visual which values from 1 to 48. When I select my value in slicer I want to get my value in Business Time % column according to the slicer value.

[MFelix]

Hi @abiyevnijat ,

You need to create a new measure to use on your visualizations, you cannot update values on a table using a slicer.

So in this case the Business Time % needs to be a measure also and not a calculated column on your model, that way you get the expected result that is dynamic based on the what if parameter measure.

[abiyevnijat]

Thanks again for your response. I created parameter which values between 1 from 48. Then I create measure 

Business Time % = 
IF ( 'Dashboard'[Business] < SELECTEDVALUE('Slicer'[Value]), 1, SELECTEDVALUE('Slicer'[Value]) / 'Dashboard'[Business] )

 In this measure my Business values is a column in my Dashboard table and I cant result Business Time % like that. Is there any method to find Business Time %, with different code or something which I can show average Business Time % in card visual, when I change my values in slicer when add parameter

[MFelix · Accepted solution]

Hi @abiyevnijat ,

For this you need to use a different measure. Try the following measure:

Business Time % =
VAR temptable =
    ADDCOLUMNS (
        'Dashboard',
        "@BusinessTime%",
            IF (
                'Dashboard'[Business] < SELECTEDVALUE ( 'Slicer'[Slicer] ),
                1,
                SELECTEDVALUE ( Slicer[Slicer] ) / 'Dashboard'[Business]
            )
    )
RETURN
    AVERAGEX ( temptable, [@BusinessTime%] )

See PBIX file attach.

[abiyevnijat]

It is completely what I want. Big thanks for you

[devesh_gupta]

@abiyevnijat 

To achieve the desired result of dynamically calculating the Business Time % based on the selected value from a slicer dropdown visual, you can follow these steps:

Create a new measure in your main table (Dashboard) using the following formula:

Business Time % = 
IF ( 'Dashboard'[Business] < SELECTEDVALUE('Slicer'[Value]), 1, SELECTEDVALUE('Slicer'[Value]) / 'Dashboard'[Business] )

1. Create a new table (Slicer) with a single column (Value) containing values from 1 to 48.

2. Add a slicer visual to your report using the ‘Value’ column from the ‘Slicer’ table.

3. Selecting a value from the slicer will dynamically update the ‘Business Time %’ measure based on the selected value and the corresponding value in the ‘Business’ column of your main table.

If you find this insightful, please provide a Kudo and accept this as a solution.

[abiyevnijat]

Thanks for answer. But, I want my Business Time  % not in measure. I want to see my values in all rows in Business Time % according to Business Column. For example, when I select 4 in dropdown which is my SLA Time, I want to see in  

Business Time % =

IF ( 'Dashboard'[Business] < [SLA Time], 1, [SLA Time] / 'Dashboard'[Business]) 
which all rows gives answer according to Business Column. I got this result whem I create SLA Time measure and change manually. But, I want do this dynamically with slicer

[MFelix]

Hi @abiyevnijat ,

For this you need to use a What If parameter.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

This will allow to have a slicer where you select the SLA time and use the metric on your calculations.

[abiyevnijat]

Thanks for quick response. I visited this link, but I cant get suitable information for my problems. I create parameter table and added to slicer,but I cant get wanted result. All rows in my column display null when I change slicer.