Power BI Can you use an inactive join for a slicer (USERELATIONSHIP)

Imagine the scenario. You are dealing with two dates. date A and B. You create an active and an inactive relationship to your date table.

You can create Dax with USERELATIONSHIP for any measures that use the inactive join. Great for the bar charts, line charts etc.

But what if you have a slicer for Year on the report? At the moment that slicer is connected to Date A because you simple drag year from the date table.

This report needs to be sliced against Date B. Is there a way to do this?

Lets look at one of our measures that uses USERELATIONSHIP

YTD Measure =

CALCULATE(TOTALYTD([Number of Records],’Date'[Date]), USERELATIONSHIP(Metrics[DateKeyB],’Date'[DateKey]))


From this it is clear you can only use it in a measure with a metric.

And you cannot use a Measure in a slicer. Slicers have to be created from a column.

Which means that our inactive dates can’t be used in a slicer. And this is problematic because if you have a page of time based visuals all using the USERELATIONSHIP then its a fair shout that you are going to have a slicer too for example ‘Year’

So can we come up with a workaround?

Workaround 1. Adding date information to the fact table

Out model rules are

  • DateKeys in the Fact table. I also hold the dates hidden just in case
  • The dateKey Joins to the date Dimension which contains Year, Day, Month etc

So what I am going to do in this case is add my relationship B details into the fact table just for the time being.

s.[DateB] AS [Date B],
--Workaround 1
DATEPART(Year,s.[DateB]) AS [Year B],
DATENAME(Month,s.[DateB]) AS [Month B],
DATEPart(Month,s.[DateB]) AS [Month No B],
[staging].[fnQuarter](s.[DateB]) AS [Quarter B],

I have created a Quarter function with the specific quarter logic which is use for every Quarter period.

This is then refreshed into the Power BI Dataflow

And then refreshed into my data set.

All ready to work with. What I decided to do in Power BI was create a table containing those values.

Support End Date (For Slicers) = 
SUMMARIZE ( 'GrowthHubMetrics', 
'GrowthHubMetrics'[Support End Quarter], 
'GrowthHubMetrics'[Support End Month], 
'GrowthHubMetrics'[Support End Month No], 
'GrowthHubMetrics'[Support End Year], 
'GrowthHubMetrics'[Support End Date])

The dates in the fact table were then hidden leaving the above Slicer table.

And then I remembered that Date time Measures don’t work without a continuous date table and DateB isn’t continuous.

But then I remembered that I had already put in a lot of work creating all the date time measures on the inactive join to the date table using USERELATIONSHIP so actually all the date time measure are fine.

Would I go back and create a date table for dateA and a date table for DateB so I can have slicers and drill down to the date. Its very possible but that renders the use of USERELATIONSHIP Pretty null and void, unless you still want to slice these visuals against DateA.

Connect the DAX table to the model.

the date Table for Slicers is connected up to Dimdate in the model and we are ready to go

So all the measures are still connected to the date table through the inactive join

But the slicer can be set up over the new DAX table

I’m hoping to get a better way of doing this as we go because it seems that you should be able to do this without having to create another table.


In the end, this became so complex and issue heavy (Drill throughs not working properly etc) I gave up. Created a reference of the date table for Date B. Connected this up. removed the DAX table and changed all my measures to get rid of USERRELATIONSHIP.

So Pages 1 to2 use DateA and Pages 3 and 4 use DATEB.

As a conclusion, if you are wanting to use Drill through and Drill down I think I would recommend role playing dimensions. the complexity of the USERELATIONSHIP with just seems incredibly complex at the moment.

Its a shame because I love the idea of switching to a none active join but in practice its proved unworkable.

If you have any tips on this please let me know

Power BI 2020 Updates. Hierarchical Slicer

We currently have a report with a hidden slicer panel, and one of the slicers is for Date. We want the user to choose the month of the report

Click on the visual and you can see that its the Hierarchical slicer from the Market place

However, Power BI February 2020 updates includes this feature on the main slicer. Can we Change out hierarchical slicer to the original Slicer and see how it looks?

Options and Settings

Currently this is a preview feature and it needs to be turned on and Power BI re -started

Use Selection Pane to check where the original slicer is

The Slicer is currently in the Slicer Group on this specific Bookmark

Now, with the Slicer visual selected, simply click on the original slicer in Visuals to change the Visual

Lets see the difference

It looks great and there is no need to add an additional visual from the market place

I’ve set single select to ON so you can only choose one month

I think this looks like a great new update and one that clears extra visuals from your reports

Power BI Time based Measure not working when slicer (Year) applied

SAMEPERIODLASTYEAR DAX not working with a year filter

I wanted to create a KPI showing year to Date and Last year to date.

So I have Measures:

YTD Complaints = TOTALYTD(‘fact Fact'[Complaints],’dim Date'[date].[Date])

LY YTD Complaints = CALCULATE([YTD Complaints],SAMEPERIODLASTYEAR(‘dim Date'[date].[Date])) 

I have a slicer on year so i can select the year to look at. Without the year selected its fine (Ive put the data into a table simply to have a look at)

As you can see the last year information is shown in the column 

if I choose 2019 to look at i want to see 2019 with Last year against it

See how the Last year value disappears. I was expecting that creating this measure would allow you to see Last years metric even with a year slicer set

The answer to this is actually quite simple

Here I have used Year which is an actual data column in my date table to slice the data with and its not working

This time I have used year from my date hierarchy and its worked

Which to me meas that if you are using these kind of time based measures, if you want to slice by date you HAVE to use the time period from the date hierarchy in the slicer rather than another data item (As in the example. I used the Year column created in the date dimension)

Create your website with WordPress.com
Get started