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).

 

The only way I could think to do this was to copy the data table so I have 2 versions of it, I could then apply the slicers to the individual data sets but I can't see a way of restricting the slicer to only look at one data table.

 

Is there a way to restrict a slicer to only look at one data table?

 

Is there a better/easier way to go about achieving this?

 

Thanks in advance for any help

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

     

9 Replies

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

    Can you show a depiction of the model and provide sample data or PBIX file?

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

      Hi Paul

       

      Thanks for your quick reply, I have built a very basic mock up of what I am trying to do. I need the slicers to work against the individual columns in the chart so I can compare the 2 values but I can't see a way of making them work independently. I have just seen that I can't upload a pbix so have added some pictures. Please let me know if you need more information.

       

      I hope this makes sense.

       

      Thanks again

       

       

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

    You can share the PBIX file via a cloud service (OneDrive, Google Drive, Deopbox....)

      • 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