Many schools might use both effort and attainment grades in their report system. We also have mock exam and final exam results, and a variety of baseline measures (e.g. CAT4 & NGRT). I want the experience for our users to be as efficient as possible and that means reducing the number of clicks to get to the information they want. With the examples above, this might mean having one report page for effort and another for attainment data. The same for mock exam and final exam data. Similarly, I might have had multiple charts on a page to show a range of baseline measures.
All of this reduces the quality of the user experience. To make it better, we can use disconnected tables and slicers to switch the measure used in a particular chart. Now, rather than having two pages to show effort and attainment, the user can select whether they want to see the attainment or effort data in a slicer and that will change the measure used in all the visuals on the page.
Setting this up is relatively simple, but requires a few different steps as outlined below.
Create your measures
First of all, you need to create the measures that you want to use in the visuals. In the effort and attainment example, they are simply:
Average of attainment track = AVERAGE('Report data'[Attainment track]) Average effort grade = AVERAGE('Report data (effort)'[Effort Grade (Number)])
Create a disconnected table
Disconnected simply means that the table we create does not have any relationships with other tables in the model. The table we create will hold the names that we want to use in the slicer. I have created one disconnected table with all different slicer names that I want to use throughout the model, but you can create them individually if you’d rather. To make it quicker and easier to modify this table, I have created it in Excel which I have then added to the model via a Dataflow, but you could create it directly in your model instead.
At this point, making a selection in the slicer won’t do anything because the table is disconnected. We need to add a few more measures to fix that. The first measure, needs to pick up and store the name of the selection made in the slicer. The SELECTEDVALUE function does what we need:
Selected measure = SELECTEDVALUE('Disconnected slicers'[Code])
In this case, I’ve chosen to store the associated code, rather than the name in the slicer. This means we can change the names used in the slicers without affecting the DAX code used in the SWITCH measure (coming up next). For example, if the user selects Attainment track in the Report measure slicer, the Selected measure measure will store the value 1 (Report measure: attainment track = code: 1)
Next up, we need to create a measure that we will use on the visuals. In plain English it will say:
- If the user selects attainment track [“1”] in the slicer [selected measure] then use the [average of attainment track] measure on the visuals
- But if they select effort grades [“2”] then use the [average of effort grade] measure on the visuals.
Here’s the code:
Report measure switch = SWITCH( [Selected measure], "1",[Average of Attainment Track], "2",[Average effort grade] )
By using the code rather than the report measure column if we change the names used in the slicer we only need to change them in the disconnected table but not the SWITCH measure.
Please note, the code column has data type text in the disconnected table, hence the need to use “” in the SWITCH measure. If the data type was numeric I don’t think the “” would be required.
To recap, the steps you need to take are:
- Create the measures you want to use on the visuals
- Add a disconnected table that contains the values you want to use in the slicer
- Create a SELECTED VALUE measure that picks up the value chosen in the slicer
- Create a SWITCH measure that swaps the measure depending on the value chosen in the slicer
- Add the SWITCH measure to your visuals.
Following this process can help to improve the experience for your users by cutting down the number of report pages or visuals you need to use.
With thanks to Radacad where I learnt this technique.