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

[wi11iamr]

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...? 🙂

[mike_honey]

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]

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]

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.

[robkopp]

Mike,

I have a use case where users want to be able to show the measure (Sum of Sales) in terms of Geography, Account Segment or Product inside the same chart. I believe your solution will work but want to understand this a bit better so I don't spin my wheels. I might not be understanding it. 

If I have a fact table which has all Sales and there is a field for Geo, Segment, Product on this fact table. I assume the bridge table needs the following structure. 

Attribute Name | Attribute Value

Geo | NA

Geo | APJ

etc

Segment | Tier 1

Segment | Tier 2

etc

Product | A

Product | B

etc

I believe I would then create a relationship between the Attribute Value and each of the fields on the fact table (Geo, Segment, Product)

The piece that is hanging me up is the unique "Fact Key" column on my fact table. The values would be unique on the bridge table so why would they need to be unique on the fact table. 

[Anonymous]

I  guess that way you need to duplicate your dataset for every single combination of dimension that users have option to because it is all about relationship then every combination you can give it a key. Choosing one dimension effectively means you select one particular dataset, say sales by region, then when user selects country too, that means you need to filter the dataset for this combination. It could be a nightmare if you have a bunch of dimensions to choose. If Power BI can make measure more flexible and useful, say put measure into columns rather than only values that will be much better. Thumb up on Qlikview on that piece, it is so easy to achieve this task in that tool as I recently switched over to Power BI.

[Anonymous]

Hi, 

I am also facing same issue.. I am working on migrating Qlik application to power BI. I have dimention levels and also I have to select dimention and dimention level to show in the Pivot chart as per selection. Do you have link which explain dimention selection concept.

---

@Anonymous wrote:

I  guess that way you need to duplicate your dataset for every single combination of dimension that users have option to because it is all about relationship then every combination you can give it a key. Choosing one dimension effectively means you select one particular dataset, say sales by region, then when user selects country too, that means you need to filter the dataset for this combination. It could be a nightmare if you have a bunch of dimensions to choose. If Power BI can make measure more flexible and useful, say put measure into columns rather than only values that will be much better. Thumb up on Qlikview on that piece, it is so easy to achieve this task in that tool as I recently switched over to Power BI.

---

[Anonymous]

No, I was just trying to conceptualise it as I thought it would streamline some reports... Basically my thought was like below. You need to create multiple datasets to mirror every possible selection user could make. Below just a sample of two fields and data would display only if both columns are selected. You also need to consider where multple columns are avaiable but not all of them are selected. Hoping someone would be able to shed some lights to make it easier as I am also hoping to implement it.

Key	Attribute1	Attribute2	Value
Combkey_1	Salesperson A	Region A	10
Combkey_1	Salesperson b	Region B	10
Combkey_1	Salesperson C	Region A	10
Combkey_2	Salesperson A	July	10
Combkey_2	Salesperson b	July	10
Combkey_2	Salesperson C	July	10

[AlexHill]

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.

[ankitpatira]

@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.