Forum Discussion

wi11iamr's avatar
wi11iamr
Icon for Advocate II rankAdvocate II
10 years ago

Select dimensions to dynamically add to chart - much like a pivot chart

I'd like to try and make a chart where the end user is able to select which dimension or measure they wish to see in the chart.

For example, let's say I'm showing the budgeted expenses per month, but then I hope to have a "tick box" with a selection of some more measures, such as the actual expenses per month, which the user can then choose to add to the chart.

 

Am I pushing the PowerBI functionality too far now...? :)

Need Help

9 Replies

  • mike_honey's avatar
    mike_honey
    Icon for Memorable Member rankMemorable Member
    10 years ago

    This is a bit tricky to set up - using a disconnected slicer table combined with the "Switch ... True ... " function pattern.  But depending on how far you want to go to please your users it might be worth it ...

     

    I would add a table listing the measures e.g. "Measure List".  If you dont have a data source for this (likely) I would use "Enter Data" and just type in the list of Measures, with arbitrary numbers using a "Measure ID" column.  The "Measure List" table must not have a relationship with any other table.

     

    Then I would create a measure using a formula like this:

     

    Measure Value = switch (TRUE

    , Min ( 'Measure List'[Measure ID] ) = 1 , Sum ( MyTable[budgeted expenses] )

    , Min ( 'Measure List'[Measure ID] ) = 2, Sum ( MyTable[actual expenses] )

    )

     

    Keep adding in as many copies of the "Min" rows as you have in your "Measure List" table.

     

    Create a slicer using the "Measure List" table. Leave it on single-select (the default) and dont allow Select All.

     

    Use the "Measure Value" result in your charts.

     

    • AlexHill's avatar
      AlexHill
      Regular Visitor
      9 years ago

      Hey mike_honey, this works great for metrics but I can't seem to get it to work with dimensions, do you know if there is a way of structuring the formula so it'll allow me to switch the Legend or AXIS on a chart as opposed to the metric?

       

      Thanks

      • mike_honey's avatar
        mike_honey
        Icon for Memorable Member rankMemorable Member
        9 years ago

        I would build a bridge table with generic "Attribute Name" and "Attribute Value" columns, appending the data from each dimension field.  The "Attribute Name" table tells you which source the row came from, the "Attribute Value" column has the dimension value. The granularity would be one row per Fact row, per Dimension field.  

         

        You'll need a unique "Fact Key" column on your Fact table, copied to the Bridge table. Create a relationship between those using "Both" cross filtering.

         

        Then you can build a Slicer on the "Attribute Name" field, and show the "Attribute Value" field in any visuals.  Your pre-existing measures should just work.

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    10 years ago

    wi11iamr In power bi service create a group and add your users to that group. You can choose edit rights for the selected users in that group. Then in power bi desktop create measures and publish reports to that group. Your users can now edit the standard report and add measures (you created) to chart they like. 

     

    I don't think or is aware of such visual yet which will let you drag and drop fields. You can also consider using drill down in charts so that your measures are one level below standard, and your users drill down to see for the measures but I don't think that is what you want. Creating groups way is your best bet i think.