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, bu...
Need Help
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.

      • AlexHill's avatar
        AlexHill
        Regular Visitor
        9 years ago

        Hey Mike - thanks so much for coming back to me.

         

        I think that makes sense to me, seems i'll need to do a bit of restructuring on my data so might take me a while to have a proper play with it. I'll report back once I've managed to grab a bit of downtime and had a go.

         

        Thanks again for your help.