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

v-kelly-msft's avatar
v-kelly-msft
Community Support
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