Forum Discussion

Topjacket's avatar
Topjacket
Icon for Helper I rankHelper I
4 years ago
Solved

Set Slicer to only work on 1 data table

Hi All   I am try to build a column chart that shows the record count for a single customer (selected by slicer) vs the average record count for a group of customers (selected by a different slicer...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Thanks for the file. See if this works for you. You only need one call table; you need two dimension tables for customer. I've called one Dim Customer and the other Group Customer. Create relationships between these tables and the call table via the customer fileds. Leave the Group Customer relationship inactive. The model looks like this:

    Now create the measures:

     

     

     

    Call Count = 
SUM('Customer Calls'[number])
    Average count by Group = 
AVERAGEX('Customer Group', 
CALCULATE (
    [Call Count],
    REMOVEFILTERS ( 'Dim Customer'[Customer Name] ),
    USERELATIONSHIP ( 'Customer Group'[Customer Name], 'Customer Calls'[Customer Name] )))

     

     

    If you want the selecion in the slicers to be mutually exclussive, you can use this measure as a filter for the Customer group Slicer:

     

    Filter GSlicer = 
COUNTROWS(
    EXCEPT('Customer Group', 'Dim Customer'))

     

    and this measure as a filter for the customer slicer:

     

    Filter Dim Slicer =
IF (
    ISFILTERED ( 'Customer Group'[Customer Name] ),
    COUNTROWS ( EXCEPT ( 'Dim Customer', 'Customer Group' ) ),
    1
)

     

    To get

     

     I've attached the sample file

     

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

Thanks for the file. See if this works for you. You only need one call table; you need two dimension tables for customer. I've called one Dim Customer and the other Group Customer. Create relationships between these tables and the call table via the customer fileds. Leave the Group Customer relationship inactive. The model looks like this:

Now create the measures:

 

 

 

Call Count = 
SUM('Customer Calls'[number])
Average count by Group = 
AVERAGEX('Customer Group', 
CALCULATE (
    [Call Count],
    REMOVEFILTERS ( 'Dim Customer'[Customer Name] ),
    USERELATIONSHIP ( 'Customer Group'[Customer Name], 'Customer Calls'[Customer Name] )))

 

 

If you want the selecion in the slicers to be mutually exclussive, you can use this measure as a filter for the Customer group Slicer:

 

Filter GSlicer = 
COUNTROWS(
    EXCEPT('Customer Group', 'Dim Customer'))

 

and this measure as a filter for the customer slicer:

 

Filter Dim Slicer =
IF (
    ISFILTERED ( 'Customer Group'[Customer Name] ),
    COUNTROWS ( EXCEPT ( 'Dim Customer', 'Customer Group' ) ),
    1
)

 

To get

 

 I've attached the sample file

 

  • Topjacket's avatar
    Topjacket
    Icon for Helper I rankHelper I
    4 years ago

    Thank you very much for this. Its brilliant!!

     

    Its doing exaclty what I need.

     

    I'm going to read up on the dimention bit as thats all new to me.

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      4 years ago

      A Dimension table is a table containing  unique values from a field in your fact table(s) with a relationship with the corresponding field in your fact table(s). They make the model more efficient among other benefits. They can be created in Power Query or using DAX. I created them using DAX with the DISTINCT function. (See the code in the sample file). 
      they are extremely useful and a recommended best practice.

      • Topjacket's avatar
        Topjacket
        Icon for Helper I rankHelper I
        4 years ago

        Thanks again Paul, this is really useful.

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      4 years ago

      To make the visual clearer, you can create a measure to use as a title with:

      Visual Title = "Count of " & SELECTEDVALUE(Dim Customer [Customer Name]) & " compared to the average count of  " & CONCATENATEX(VALUES(Customer Group [Customer Name]), Customer Group [Customer Name], ", ")

      Select the visual, go to Title under General in the formatting pane, select the fx icon and under value select [Visual Title] measure