Slicer to Exclude

[Anonymous]

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?

[BA_Pete]

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

[Anonymous]

Hi Pete,

Unfortunately I can't do that as I have to make a histogram out of the summarised table.

[BA_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

[Anonymous]

Hi @BA_Pete,

So there's 2 things I want to do.

1. I want to be able to filter with the summarised table with 'Type' using a slicer based of the original table, as showcased above. 

2. Based on the sumarised table, I want to make a histogram with 'Amount' bins on the x-axis and frequncy on the y-axis ( which comes by placing the 'Amount' in the values section). I also want to be able to filter using 'Type' from the original table.  

[BA_Pete · Accepted solution]

@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

[Anonymous]

Hi @BA_Pete,

When you say merge ID and DayID is this right?

groupID= CONCATENATE('Table'[ID], 'Table'[DayID])

And then form the summarised table using

sumTable= SUMMARIZE(table, table[ID],table[DayID],table[groupID],"Amount", sum(table[amount]))

Is this right?

[BA_Pete]

Hi @Anonymous ,

I would do both in Power Query myself:

// Call this idTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWAjIOGoFKuDEDIxABJOcCFjkLABiiqQkBFIyBksZAQVMgYJucCFTGBmOaKoMkVoNIaqMkPYCBMyh6qKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DayID = _t, Amount = _t, Type = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DayID", Int64.Type}, {"Amount", Int64.Type}, {"Type", type text}}),
    addGroupID = Table.AddColumn(chgTypes, "groupID", each Text.Combine({Text.From([ID], "en-GB"), Text.From([DayID], "en-GB")}, "-"), type text)
in
    addGroupID

// idTableSumm
let
    Source = idTable,
    groupRows = Table.Group(Source, {"ID", "DayID", "groupID"}, {{"Amount", each List.Sum([Amount]), type nullable number}})
in
    groupRows

But if you don't have access to PQ, then I would do the following DAX for the [groupID] field (just to make sure there's no bleed between the ID's):

groupID = [ID] & "-" & [DayID]

Your SUMMARIZE DAX looks correct already.

Pete