+ Reply to Thread
Results 1 to 2 of 2

Data model - table relationships and filtering

  1. #1
    Registered User
    Join Date
    03-05-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    20

    Data model - table relationships and filtering

    Hi forum experts,

    I'm working with a power pivot data model as shown here. I'm using some simple measures:

    Count the number of distinct activities:
    Distinct Activities = calculate(DISTINCTCOUNT(ServiceDetails[Activity ID]))

    Assign a status based on the volume of activities:
    Service Status = if([Distinct Activities]>=5,2,IF([Distinct Activities]=0,0,1))

    I have a pivot table with a slicer to select "Consultant Name" and show the activities and status for that consultant against different accounts in rows ("Master Group").

    The RHS table contains feedback from sales people (Addressable? Yes/No) on whether consultants should be servicing specific accounts. I want to add a second slicer to my pivot table based on this field so that I can filter the view for "Yes" vs. "No" results.

    Since the directionality is going the wrong way, I made a CROSSFILTER measure. But clicking Yes or No on the slicer generates different values instead of just filtering the view.

    Addressable Service Status = CALCULATE([Service Status], CROSSFILTER (EmployeeTeams[Consultant Name], SalesFeedbackTable[Consultant Name], Both))

    I'm a novice at this and wonder what I'm doing wrong. Advice very much appreciated.

    datamodel.jpg

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,570

    Re: Data model - table relationships and filtering

    Not saying that I can help solve, however it may be easier for someone to help if we had access to a sample Excel file.
    Information as to attaching an .xlsx file to a post is given in the banner at the top of the page.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot Table Slicer Field names (Data model vs Non Data Model)
    By NewYears1978 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-02-2021, 10:52 AM
  2. Powerpivot Calendar Table w/ one to many relationships not working in pivot table
    By lastobie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2021, 04:09 PM
  3. Data Model Relationships Not working as expected?
    By ptmuldoon in forum Excel General
    Replies: 5
    Last Post: 01-08-2021, 04:39 PM
  4. Pivot Table and Data Model
    By olga6542 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-17-2018, 02:43 PM
  5. Replies: 0
    Last Post: 10-19-2017, 10:37 AM
  6. Add this data to the Data Model addition needed to current pivot table code
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2017, 06:52 PM
  7. Pivot table relationships between source table data
    By LizzyJ in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-13-2015, 09:56 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1