Measures/Slicers - filtering direction complications

[gambleave]

Hi experts, I'm quite new to Power BI and Power Pivot,  and would be grateful for  advice on a project giving me headaches at work (but which is probably dead simple to someone competent!). I've tried to approximate it with a simple model in Power Pivot (don't have Power BI on my home laptop) shown below and link here to the file on google drive (need to save to Excel)

The rough situation - we have a team of consultants and a team of sales people serving clients (in Master Group > Business Entity hierarchy). Mutliple sales people can serve as account managers for a client, and multiple consultants service the same accounts.  Clients are tiered at a Master Group level, but individuals within the Business Entities "vote" periodically for consultants who have provided good service. We have surveyed sales people to indicate for each consultant whether a client (Master Group) should be treated as a priority (SalesFeedbackTable[Priority?]).

From all this, I want a dashboard that can slice by tier, by consultant, and by Priority (Yes/No) to show measures such as the following:

- Number of distinct high touch (ServiceDetails[Service Type]="HT") activities in the past 12 months

- Days since last high touch interaction (using calculated DATEDIFF column and MIN measure)

- Names of clients who received hight touch service in past 12 months

I also assign a status to the account relative to the consultant, by nesting some measures:

- Service Status: returns a "2" if 3 or more interactions are counted (threshold considered significant to earn a vote), a "1" if 1 or 2 interactions are counted and "0" if no interactions.

- Vote Binary: returns a "1" if a vote is recorded for the consultant from any business entity under the master group, or a "0"

- Account Status: returns a number for different combinations of Service Status and Vote Binary (e.g. significant serviced and voted, no service, no vote). Values representing no vote ("0", "1", "2") are considered to be gaps for the consultant.

The problem is that the slicer for Priority? does not do anything because of the filter direction. I tried to work around with a CROSSFILTER measure, but this filters the underlying data and causes my status measures to change. I just wanted the slicer to toggle between a view of Priority and non-Priority accounts!

I also want to come up with some benchmarking measures and am not sure what type of measure can iterate to achieve this:

- Count the number of gaps at consultant, sector, team-wide levels

- Count the number of non-voting priority accounts at consultant, sector, team-wide levels that have received high touch service within the past 12 months.

The aim is to chart these benchmarks over time as part of an initiative to reduce gaps. Really appreciate any advice here!

[lbendlin]

As you noticed your SalesFeedbackTable is a "fact" table, and its filters cannot influence any other tables.  You can try to change the filter direction for the MasterGroup table to "both" but that may have unintended consequences.

You  may also want to consider conflating the MasterGroup and MasterEntities tables to simplify your data model

[gambleave]

Indeed changing the filter direction from the SalesFeedbackTable to both causes the values for existing measures to change (since the underlying data for the calculation of measures is changed). What kind of setup is required to allow for a working slicer based on a column (Priority?) in this table? 

Good point on merging the MasterGroup and MasterEntities table, but I don't think it would have an impact on this issue?

[lbendlin]

You may need to rewrite your measures to accommodate for the change in filter direction.  Also read up on CROSSFILTER() options.