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 Pete,

 

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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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

    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's avatar
      BA_Pete
      Super User
      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?