"Behind the scenes" slicer logic?

[bpflueger]

I'm building a dashboard displaying some quarterly KPIs and metrics for a group in my organization.  I want users to be able to select a single quarter (e.g. Q1 2017) from a slicer on the dashboard and see all of the KPIs and metrics achieved that quarter.

One KPI is based on counts from both the current and previous quarters.  To give a simple example, it is a percentage calculated as [Count of Support Cases Closed Current Quarter] / [Count of Support Cases Opened Previous Quarter].  My problem is that if I put a slicer on the dashboard and the user selects Q1 2017, the opened cases from Q4 2016 are filtered out so I cannot calculate the denominator.

I can obviously get the right numbers by hardcoding dates into my filters/measures/columns.  I could also do this by having users select multiple quarters from multiple slicers (one for open date, one for closed date), then unlinking them from non-applicable visualizations.  Ideally, though, I want to make this a user-proof dashboard with a single quarter selection and a set of dymanic/generic measures and columns.

So, is there any way to add logic to a slicer such that it will consider multiple values behind the scenes?  Is there any way to access the value selected in a slicer from a measure, to perform logic on that end?  Or is there an easier way to do this (via link tables or something) that I'm not seeing?

Thanks, and apologies if this is a dumb question 😉

[dearwatson]

To expand on what Eric is showing:

If you have contigeous dates where every date has data in the model you can take advantage of time intelligence functions in DAX.

In this case you could use PREVIOUSQUARTER to automagically determine the users current Q select and calculate the metric for the previous quarter, this is a snap and very handy.

Add a calendar/date dimension table and link it to your [support case date] if you don't have running dates... then use the CALCULATE([Count of Support Cases], PREVIOUSQUARTER(Calendar[Date])) as per Erics example... you can do this for all sorts of cool time related calcs and saves hardcoding anything.

[bpflueger]

Thank you for the replies and example!

After tinkering with the example, I think it only gets me partway there.  I'm still struggling with how to deal with multiple date columns in the context of a single slicer.

I can't share my actual data, as it contains some sensitive stuff, but as a very simple example it looks something like this:

I also have a calendar table that looks like this:

There is a relationship between [Open Date] in the first table and [Date] in the second.  Power BI won't let me set a relationship with [Close Date] at the same time for obvious reasons.

I have three measures that I calculate:

1. The denominator, the number of cases that were opened -1 quarter from the value selected in the slicer.  Working fine with the suggested PREVIOUSQUARTER logic.
2. The target, 90% of the cases opened last quarter.  Also working fine.
3. The numerator, the number of cases that were closed in the quarter selected in the slicer, regardless of when they were opened.  This is where it falls apart - when I select a quarter in the slicer, since it is linked to the Open Date, anything that was not ALSO opened in that quarter is not counted.

So, for an example from the sample data above, let's say I want to calculate this KPI for Q2 2016.  There was one case closed in Q2 (#5678) and three cases opened in the previous quarter Q1 (#1234, 5678, 9012).  If I select Q2 from the slicer, however, #5678 gets filtered due to its Open Date and isn't counted towards closed cases.  It won't always be this simple, where it's offset by a single quarter - it may be multiple quarters or even years.

Basically, if a case was not both opened and closed in the same quarter, it is being missed.  I know this is due to how my relationship is setup, but how do I get around this?

The simple solution would be to link my date table to the Close Date, however, Open Date is what every other metric on this dashboard relies on - this one KPI is the outlier.  I could also put it on a separate page with a separate slicer, but want to avoid that if possible.

I'm 99% sure I'm just overthinking this and/or doing something really spectacularly dumb... but I cannot figure it out.  Any ideas?  Any other info from my setup that I can share?  Thank you again!

[mattbrice · Accepted solution]

While it is true you can only have one active relationship between two tables, you can have any number of inactive relationships.  So if you create a second, inactive relationship between the Calendar[Date] column to Table[Close Date] (dotted line connector = inactive) you should be able to get your denominator like this:

KPI denominator = CALCULATE ( SUM (Table[Value]), USERELATIONSHIP ( Table[Close Date], Calendar[Date] ) ) 

This tells Dax to use the inactive relationship for just this measure.  

[bpflueger]

Thank you so much, USERELATIONSHIP was the silver bullet!

I wish I could mark two solutions - thanks again for the tips on PREVIOUSQUARTER as well 🙂

[Eric_Zhang]

@bpflueger

You can check below demo and tweak your measures accordingly. Check more details in the attached pbix.

value of currect selected Quarter = SUM('table'[Value])


value of previous Quarter currect selected = CALCULATE(SUM('table'[Value]),PREVIOUSQUARTER('calendar'[Date]))


KPI = [value of currect selected Quarter]/[value of previous Quarter currect selected]

[mattbrice]

For anyone to help we'll need more info.  What is the code for the meaures you have?  Model deisgn and layout?  Are you using a Calendar table?  Time Intelligence functions?

What you want to do should be fairly easy, but without some data I'd only be guessing on what needs to be done.