Dynamic What-if modelling

[tomkummerow91]

Hi!

I'm trying to recreate a model which I've built in excel in Power BI. Basically it takes inputs from a series of drop down lists (will need to be slicers in PBI) and creates a variable based on the selections made in those slicers. I've been trying to get it to work using What-if parameters, but when I do this the slicer selections don't just change the variable/measure in the background, but filters anything which is not selected from the slicers out of the graphic in the power BI report as well. Below is a link to an excel file with a demo of what I'm trying to achieve. The values in Column H on the Data worksheet are based on the selections made from the drop down lists in column B of the Modelling worksheet. This then changes the RHS chart.

https://drive.google.com/open?id=1jRKcoRzss2ekJevdTgaeadbuZFnJ9vga 

Any help is appreciated, TIA!

Tom

[v-kelly-msft]

Hi @tomkummerow91 ，

You need first create a relationship between the 2 tables, then put the columns of table data which you want to be filtered into a table visual, finally put the column of table modelling you wanna filter by into slicer, which are shown as below:

Best Regards,

Kelly

[amitchandak]

I did not get the formula, But seems like some % has been calculated.  In the row with GT

in Power BI for GT

GT = Calculate(sum(table[amount]),all(table[amount]))
OR
GT = Calculate(sum(table[amount]),all(table[amount])

In case you need a specific formula, please share the logic.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[TomMartens]

Hey @tomkummerow91 ,

you have to use parameter tables that are not related to the tables that build your data model. You can create these un-related tables using DAX if you need the values.

Hopefully this gets you started.

Regards,

Tom

[tomkummerow91]

Thanks @TomMartens , The issue I have if I un-relate them is that my measure which creates the value for the Model_tier column no longer works. The value of this column needs to be dynamic and dependent on the values selected in the slicers. I've created another version in a Pbix file to share. The chart on the left should always stay static. The chart on the right should change according to the selections made in the slicers. In excel I would just make this as a calculated column based on the slicer selections but this doesn't seem possible in PBI.

To demonstrate, if the slicer selections shown below were made, I would want the 6.45% in BND/GOVT to move to Tier 3 in the RHS chart. There would then be less in Tier 1 and more in Tier 3 for the right chart compared to the left chart.

Does this help explain it at all?

[v-kelly-msft]

Hi @tomkummerow91 ，

You need to create a new table as parameter list, then create a measure to build a relationship between the parameter and the table you wanna filter by.

Measure is as below:

Measure 2 = IF(SELECTEDVALUE(slicer[Group])=SELECTEDVALUE(Data[Concat]),1,0)

Then put the measure together in the table visual with the table you wanna filter by, and in the filter selection,choose the result equals 1:

For the related .pbix file,pls click here.

Best Regards,

Kelly

[tomkummerow91]

Thanks @v-kelly-msft , but I'm not seeing how that works for more than one slicer or the graphics I showed the picture of. There Pbix file I've done up can be found here. Are you able to show how exactly your methodology would work?  

Thanks

Tom 

[v-kelly-msft]

Hi @tomkummerow91 ,

You need to create a measure as below:

Measure = 
var a =SELECTEDVALUE(Portfolios[Portfolio])
Return
IF(SELECTEDVALUE(Data[Portfolio])=a,1,0)

Then put the measure together with other columns you wanna filter by in a visual in the values pane ,and in the filter selection, choose measure =1,finally  you will see:

For the related .pbix file,pls click here.

Best Regards,

Kelly

[tomkummerow91]

Thanks @v-kelly-msft . So for your method to work, I would need to create a measure for each individual combination of characteristic selections and then filter on these? I think this would require creating hundreds of measures which would need to be regularly updated if there were new fields. Do you agree?

Thanks

Tom 

[v-kelly-msft · Accepted solution]

Hi @tomkummerow91 ,

You can use one measure instead of multiple measures to realize what you need:

AxisControl Measure = 
IF(ISFILTERED('Tier Slicer'[Normal Tier]),
IF (
    SELECTEDVALUE ( 'Axis Table'[Concat] )
        IN DISTINCT ( 'Data Slicer Table'[Concat] )
        && SELECTEDVALUE ( 'Axis Table'[Normal Tier] )
            IN FILTERS ( 'Tier Slicer'[Normal Tier] ),
    1,
    IF (
        SELECTEDVALUE ( 'Axis Table'[Concat] )
            IN DISTINCT ( 'Data Slicer Table'[Concat] )
            && NOT SELECTEDVALUE ( 'Axis Table'[Normal Tier] )
                IN FILTERS ( 'Tier Slicer'[Normal Tier] ),
        -1,
        IF (
            CALCULATE (
                COUNTROWS ( 'Data' ),
                FILTER (
                    'Data',
                    'Data'[Concat] IN FILTERS ( 'Axis Table'[Concat] )
                        && 'Data'[Normal Tier] IN FILTERS ( 'Axis Table'[Normal Tier] )
                )
            ) > 0,
            1,
            -1
        )
    )
),IF (
            CALCULATE (
                COUNTROWS ( 'Data' ),
                FILTER (
                    'Data',
                    'Data'[Concat] IN FILTERS ( 'Axis Table'[Concat] )
                        && 'Data'[Normal Tier] IN FILTERS ( 'Axis Table'[Normal Tier] )
                )
            ) > 0,
            1,
            -1
        ))

For the related .pbix file,pls click here.

Best Regards,

Kelly

[tomkummerow91]

Thanks @v-kelly-msft , that does the trick!

[v-kelly-msft]

Hi @tomkummerow91 ,

It’s no need ,you can simply change the “interactions” between the slicers and the visual. Steps are as below:

1.Go to the “Model”view, create the relationship between the slicer and the visual.

2.Choose the visual, then go to the menu bar, click “format”-> “Edit interactions”,then choose “filter” as below.

Best Regards,

Kelly