Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
Show and Tell
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
BA_Pete
Super User
4 years ago

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