Re: Creating Date Relationship Between 2 Tables

Anonymous · ‎03-21-2018

I have a generic date table I've created and another table with dates on which a survey was taken. I'm trying to get to a point where I have one date slicer that controls/updates all tiles and graphs on my page. Currently, the Date slicer changes my graph because the value on my graph contains a formula that references both the date field from my Date table and a date field from my survey data (EMS NPS 2 dataset). The Date slicer works like I want it to for the graph, but since other tiles are not referencing my date table the values stay constant no matter where I move the date slicer. I'm sure it has to do with setting up some kind of relationship between the two tables as far as dates goes but I'm confused because I've tried every combination of one-to-one, one-to-many, single, both etc. and nothing has worked so far. How can I fix this? Here are some screenshots:

Moving just my date slicer. I want the tiles to update as well but they do not:

When I move my Start Date Slicer:

As you can see, now the Passive Count and Detractor Count tiles have updated. The graph moves when I use either slicer, even though it behaves differently depending on which slicer I use. I like the way it moves when I use my Date slicer vs. my Start Date slicer. Here is the formula that I use on my graph, if it helps:

Using the Start Date slicer updates everything I need on the page, but causes the graph to behave funny. For instance, since my axis is using Month End from the Date table, if I move the Start Date slicer to 9/30/2017, it will give me the correct value on the graph for that point in time, however the date will still show today's date (3/21/2018) instead of the Month End (9/30/2017).

I apologize for the winded message, all in all I just need to figure out how to have one date that controls everything without error. Thanks!

v-xjiin-msft · ‎03-21-2018

Hi @Anonymous,

First according to your graph formula, it is based on the condition 'EMS NPS 2'[Start Date] <= MAX('Date'[Date]) && 'EMS NPS 2'[Start Date] >= (MAX('Date'[Date])-365). It also means that the Start Date is based on the condition of 'Date'[Date]. And the Axis is the Month End from Date table. Right?

Then I think you just need to use Date table to filter the graph. Why are you using Start Date?

For the two measures, they can only be filtered by Start Date slicer not the Date slicer. Then how did you define the two measures? Creating a relationshop should be a way to resolve your issue. And what's the date range in your Date table?

Thanks,
Xi Jin.

Anonymous · ‎03-22-2018

@v-xjiin-msft I was using the start date on my slicer because when using just the regular date slicer the only thing that moves on my page is the graph. I'm currently in a situation where I would have to use a slicer using the start date and a slicer using just the date to get desired results. I will show you through screenshots:

Date Slicer from Date Table at current Date:

Date Slicer pushed back to a random date:

Only the graph changed but not the tiles. I'm not sure what I need to do to get the tiles on the same page. Any relationship I attempt to establish causes everything to function incorrectly. I'll show just one example, but I've tried them all: