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

tomkummerow91's avatar
tomkummerow91
Regular Visitor
6 years ago

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

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

    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'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

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      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