Forum Discussion

tomkummerow91's avatar
tomkummerow91
Regular Visitor
6 years ago
Solved

Dynamic What-if modelling

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 ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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

TomMartens's avatar
TomMartens
Super User
6 years ago

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's avatar
tomkummerow91
Regular Visitor
6 years ago

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?