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?

     

     

     

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

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