Power Pivot, slicer and table relationships

%3CLINGO-SUB%20id%3D%22lingo-sub-3454209%22%20slang%3D%22en-US%22%3EPower%20Pivot%2C%20slicer%20and%20table%20relationships%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3454209%22%20slang%3D%22en-US%22%3E%3CP%3EReally%20appreciate%20any%20advice%20here.%20I%20have%20a%20Power%20Pivot%20data%20model%20as%20shown%20below%20for%20a%20consulting%20business%20scenario.%20I%20have%20several%20measures%20such%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDistinct%20Activities%20%3D%20calculate(DISTINCTCOUNT(ServiceDetails%5BActivity%20ID%5D))%3C%2FP%3E%3CP%3EService%20Status%20%3D%20if(%5BDistinct%20Activities%5D%26gt%3B%3D5%2C2%2CIF(%5BDistinct%20Activities%5D%3D0%2C0%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%E2%80%9CService%20Status%E2%80%9D%20measure%20is%20used%20to%20set%20up%20conditional%20formatting%20icons%20to%20indicate%20the%20frequency%20of%20service.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20recently%20surveyed%20sales%20people%20for%20feedback%20on%20which%20accounts%20(i.e.%20ServiceDetails%5BMaster%20Group%5D)%20are%20relevant%20to%20be%20serviced%20by%20particular%20consultants%20(SalesFeedbackTable%5BAddressable%3F%5D).%20I%20want%20to%20set%20up%20a%20slicer%20based%20on%20this%20field%20to%20toggle%20the%20pivot%20table%20between%20views%20of%20addressable%20(Yes)%20and%20(No)%20accounts.%20%26nbsp%3BIn%20an%20attempt%20to%20do%20this%2C%20I%20used%20CROSSFILTER%20to%20create%20the%20measure%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EService%20Status%20-%20Addressable%20%3D%20CALCULATE(%5BService%20Status%5D%2C%20CROSSFILTER%20(EmployeeTeams%5BConsultant%20Name%5D%2C%20SalesFeedbackTable%5BConsultant%20Name%5D%2C%20Both))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20just%20toggling%20the%20views%20between%20%E2%80%9CYes%E2%80%9D%20and%20%E2%80%9CNo%E2%80%9D%20accounts%2C%20the%20%E2%80%9CService%20Status%E2%80%9D%20measure%20now%20outputs%20different%20values%20depending%20on%20the%20selection%2C%20with%20all%20rows%20always%20displayed.%20I%E2%80%99m%20sure%20I%E2%80%99ve%20misunderstood%20the%20use%20of%20CROSSFILTER%E2%80%A6%20Any%20idea%20how%20to%20achieve%20what%20I%E2%80%99m%20trying%20to%20do%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-right%22%20image-alt%3D%22D380A1C0-527D-455F-A24F-232A5DF495C2.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22D380A1C0-527D-455F-A24F-232A5DF495C2.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F376658i8397C7CEA7FCAF7C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22D380A1C0-527D-455F-A24F-232A5DF495C2.jpeg%22%20alt%3D%22D380A1C0-527D-455F-A24F-232A5DF495C2.jpeg%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3454209%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Really appreciate any advice here. I have a Power Pivot data model as shown below for a consulting business scenario. I have several measures such as:

 

Distinct Activities = calculate(DISTINCTCOUNT(ServiceDetails[Activity ID]))

Service Status = if([Distinct Activities]>=5,2,IF([Distinct Activities]=0,0,1))

 

The “Service Status” measure is used to set up conditional formatting icons to indicate the frequency of service.  

 

We recently surveyed sales people for feedback on which accounts (i.e. ServiceDetails[Master Group]) are relevant to be serviced by particular consultants (SalesFeedbackTable[Addressable?]). I want to set up a slicer based on this field to toggle the pivot table between views of addressable (Yes) and (No) accounts.  In an attempt to do this, I used CROSSFILTER to create the measure:

 

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

 

Instead of just toggling the views between “Yes” and “No” accounts, the “Service Status” measure now outputs different values depending on the selection, with all rows always displayed. I’m sure I’ve misunderstood the use of CROSSFILTER… Any idea how to achieve what I’m trying to do?

 

D380A1C0-527D-455F-A24F-232A5DF495C2.jpeg

0 Replies