Forum Discussion
dynamic dimension
Hi,
Below are some of the steps I've taken and got stock in step 3
Step 1 - I created a Metric Selection table as per below
Step 2 – I created the measure: Metric Selected = SELECTEDVALUE('Metrics Selection'[Metric],"Country")
Step 3 – I tried creating the Selected Metric = SWITCH(TRUE(),
[Metric Selected]= "Department",,
I got stock at the 3rd step which I hope someone can help me figure out the next steps and how I can achieve my goal as explained below.
My aim is to create a slicer as below using the Metric column highlighted above and based on the Metric Selected in step 2
Intended Output
Department | Full Time Employees | Part Time Emp |
Admin | 67 | 15 |
HR | 12 | 4 |
Legal | 18 | 10 |
Accounts | 16 | 5 |
Finance | 41 | 3 |
Above is my intended output. So each time I select any of the metrics in the slicer, I will expect the dimension to be changed and displayed on the first column as above. When I select Department, I expect a full list of all the departments against the measures Full time & Part time. Same output expected as below when I select country from the slicer to show all the corresponding measures.
Country | Full Time Employees | Part Time Emp |
United Kingdom | 80 | 2 |
Australia | 8 | 8 |
USA | 9 | 20 |
Mexico | 10 | 8 |
Finance | 30 | 16 |
17 Replies
Hey Emmy66 ,
Unfortunately it it not that simple to create a solution that will meet your expectation.
This is because of the following.
A visual has a "structure", sometimes this structure is called aesthetics.The structure defines the what column is used on the x-axix, what column is used as legend, etc. The structure of a visual can not be changed using DAX.
This means there is no way to "switch" the column that is used in your visual, based on the slicer selectionSolving the problem requires some kind of trick.
Create a table with two columns, one column that contains the content used inside the slicer, and the other the values from the different columns.
Now you can use the second column as axis or column inside the table visual.
You have to create measure that helps to filter the values column accordingly.
You have to create a measure that is using the axis value to filter the different columns and aggregate your values.
The more "variables" you are using the "longer" the measure will get, as each column needs it's own segment in the measure.
If you need more guidance, please create a pbix file that contains sample data, but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link.
Regards,
Tom
Hi TomMartens , thanks for responding to my question. From your explanation, there isn't an easy approach to it. I'm not sure how I can accomplish the table as you suggested since my measures are calculated on the fly.
I might result to using them as individual slicers rather than combining all the demensions as another option.
Thank you once again for your suggestions.
Hi Emmy66,
Tom provides a cool workaround for this problem. Another alternative is to use bookmarks to create different views of the page, where you display different tables based on your selected bookmark. Essentially this means placing a different copy of the table in each bookmark with the categories you want to display, whilst hiding the "unused" tables. You can then toggle which bookmark to display through for example a button with an action.
It can be a little fiddly to set up but it's quite straight forward. This video is a few years old but the process is pretty much the same:
https://www.youtube.com/watch?v=_Afcj8mT5_Q&feature=emb_logo
Hope this also sheds some light on potential solutions!
Thanks Anonymous for your suggestion. Much appreciated. Though the link was very helpful but it's not exactly what I wanted.
Hi Emmy66
In addition to TomMartens 's reply, I think you can use a measure to filter your data table.
Due to I don't know about your data table, I build a sample to have a test.
Sample Table:
Metric Selection table:
Measures:
Metric Selected = SELECTEDVALUE('Metric Selection'[Metric],"Country")Filter = SWITCH(TRUE(),MAX('Table'[Category])='Metric Selection'[Metric Selected],1,0)Build a table visual, add Filter Measure into Filter Field and set it show items when value =1.
It will show country by default.
If you select Department:
If this reply still couldn't help you solve your problem, please show me your data table and more details about your requirement.
You can download the pbix file from this link: File
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Emmy66
here is a pbix file that contains sample data, there is a report page called "dynamic axis content".
The Table visual and the Stacked bar chart visual are using the column "Axis Label" from the table "Axis content".
The Slicer is using the column "Slicer".
The table is based on a DAX statement, which means whenever the underlying data model will be refreshed the content of the table will be updated as well.
The table FactOnlineSales contains a measure called "dynamic axis measure" this measure maps the axis content to the "real" columns, the "real" tables in your data model.
There is another maybe the goriest detail that makes this approach work. This detail is called lazy evaluation. The evaluation of a condition in DAX expressions is immediately stopped when the whole condition (ANDs and ORs are used) can not become true anymore or the whole expression already is true.
When the slicer selection results in Product Category, the variable checkContinent equals zero. This means that the 1st part of the condition for the continent filter part already stops the evaluation.
The downside of this approach is that each value of the slicer needs its own filter expression.Hopefully, this provides what you are looking for, at least provides some new ideas.
Regards,
Tom
Hi TomMartens thanks for the suggestion and also taking the time to assist. I have gone as far as creating 2 columns using the AddColumns/UNION in your pbix file and similar to the layout by Anonymous but my challenge is how to get the measures into the table. FullTime Employees and PartTime are all individual calculated measures and I've got 7 measures in total to add to the table. Any idea how I can accomplish this please.
