Forum Discussion
Slicer to Exclude
I have the original dataset as below
| ID | DayID | Amount | Type |
| 1 | 1 | 12 | A |
| 1 | 1 | 40 | B |
| 1 | 3 | 10 | A |
| 1 | 3 | 20 | C |
| 2 | 3 | 30 | D |
| 2 | 4 | 40 | A |
| 2 | 3 | 50 | C |
| 3 | 4 | 60 | B |
| 3 | 4 | 70 | C |
I have created a summarised table grouped by ID and DayID
| ID | DayID | SumAmount |
| 1 | 1 | 52 |
| 1 | 3 | 30 |
| 2 | 3 | 80 |
| 2 | 4 | 40 |
| 3 | 4 | 130 |
Now I want to be able to use a slicer filter with 'Type' options from the original table to filter 'SumAmount' in the summarised table. I want to exclude the ticked 'Type' option from the summarised table. For example
If I tick 'Type' A in the slicer, the summarised table should look like:
| ID | DayID | SumAmount |
| 1 | 1 | 40 |
| 1 | 3 | 20 |
| 2 | 3 | 80 |
| 3 | 4 | 130 |
Can anyone please help with how I can do this?
Anonymous ,
Ok, I see.
In that case, I would create a [groupID] column by merging the [ID] and [DayID] fields in the unsummarised table ('table').
Then reference this table to create your summarised table ('summTable') by grouping on [ID], [DayID], [groupID], and SUM[Amount].
Send to model and relate table[groupId] to summTable[groupID]. Change relationship filter direction to BOTH.
Use table[Type] as your slicer field, summTable[Amount] (bins) as your X axis, and COUNTROWS(summTable) as your frequency measure on Y axis.
*NOTE* Ensure you understand the behaviour of this model setup. Using an unsummarised dimension to filter summarised data may give you unexpected results i.e. the single [Type]s selected in the slicer will output entire summarised groups, not just values specifically related to that [Type].
Pete
7 Replies
Hi Anonymous ,
The simplest way to do this:
Do not summarise your table and keep all the fields.
Create a measure: SUM(yourTable[Amount]).
Put [ID], [DayID], and your new measure into your visual.
Set up your [Type] slicer with the 'Select All' option on, and turn off 'Multi-select with CTRL' option. Then the end user can select all slicer values and deselect the one(s) they don't want.
Pete
Hi Anonymous ,
The unsummarised table structure will still work fine for a histogram, just put only [ID]and [DayID] on the axis and use your measure for Values. Alternatively, you can right-click on fields in the Fields list and select 'New group' to bin your values however you like.
Perhaps I'm misunderstanding your use case?
Pete