This may seem a bit weird but it makes sense to me.
I am producing a complex chart and a simple table based on the product dimension. Everything is done. Now I have been asked to add a slicer to change the dimension the chart and table are based on.
All of the measures - thats the easy bit. I can calculate based on the slicer value.
The difficult part (1) is the line and stacked column chart - I use an attribute of a dimension [Product Name] as the Shared axis (x-axis) withe Sales as the column value and a cumulative for the Line Value.
How can I replace the [Product Name] with something more dynamic - so from the slicer the user selects “Sales Area Name” rather than “Product Name” and suddenly the x-axis is the Sales Area Name?
Problem number 2 is the Matrix table which has the same problem. The first column is [Product Name] and again I want that to be dependant on a slicer selection.
I want a column that would be
Column for the X Axis =
SELECTEDVALUE(‘slicer analysis’[x axis]),
“Area Name”, VALUES(‘dim Area’[Area Name]),
“Product Name”, VALUES(‘dim Product’[Product Name]),
VALUES(‘dim Product’[Product Name]) // DEFAULT
I have seen some examples where people have used a slicer, a bi-directional join to the fact table but … Product Name and Area Name are not store on the fact table. They both use a surrogate key to hook up to the fact table and as the fact table is large I want to try and avoid dulicating it.
The only other alternative I can see is using bookmarks but I want the user to look at a slicer and for that to drive the chart and Matrix.
All suggestion gratefully received. Although I have said there are two choices - in reality I know this is going to expand to 10 very rapidly.
Sorry to waffle on but I think a solution would need
- to look like they are using a slicer to pick the attribute used for Analysis
- Would be nice to avoid bookmarks.