Update active relationship globally

[sjt003001]

Hi,

We have a requirement around modeling time zones that I'm having trouble with.

There is sales data that needs to be reported in both local and headquarters time zones.  We have both a date and time dimension.  Our original solution was to create two role-playing date and time dimensions (Local Date, HQ Date, Local Time, HQ Time).  The user then needs to adjust the reporting to the time zone they want by changing the dimensions that are sliced on.  

They would like us to figure out a way to do this with a simple dimension so that they can just toggle between Local and HQ time.

I believe I need to do this by removing the multiple dimensions and having one active relationship each (HQ Date and HQ Time) and one inactive relationship each (Local Date and Local Time).  Then add a calculated table with one attribute "Time Zone" with options Local and HQ.  Each measure would need to have USERELATIONSHIP added for both date and time - If Time Zone selected = HQ just do the base calculation (active relationship), else USERELATIONSHIP for the inactive relationship on date and time.

But I'm wondering if there is some way to kind of globally set the active relationship.  So if the user selects Local, there is someway I can just make the inactive relationship active and vice-versa.  Then there is not the complication of adding a bunch of code to every measure.

Thanks,

Scott

[VasTg]

@sjt003001 

The approach of role playing dimension is correct and I wouldn't venture out to modify all the measure to check for selection and do the rest.

Here is an approach.

Lets say you have two timezones as PST and CST. I hope you know that powerbi considers all the data and time in UTC.

So you will have two Dimensions of date. PST Date and CST Date. You have to create a bridge/linker table as shown below that connects the data dimensions and facts.

Bridge_Table

Date

PST Date

CST Date

Define the relationships as below.

PST Date Dimension to Bridge_table(PST Date)

CST Date Dimension to Bridge_table(CST Date)

Bridge_table(Date) to all facts.

This approach is scalable because you could add more data column and data dimension if needed.

Let us know if you have any questions.

Edit:

I missed out the slicer for Time Selection. The above approach won't work expect creating a bridge table  with little modification. Instead of two columns for dates, you have to append the dates with flag.

Date - UTC	CST/PST	Flag
2/6/2019 0:00	2/5/2019 18:00	CST
2/6/2019 1:00	2/5/2019 19:00	CST
2/6/2019 2:00	2/5/2019 20:00	CST
2/6/2019 3:00	2/5/2019 21:00	CST
2/6/2019 4:00	2/5/2019 22:00	CST
2/6/2019 5:00	2/5/2019 23:00	CST
2/6/2019 6:00	2/6/2019 0:00	CST
2/6/2019 7:00	2/6/2019 1:00	CST
2/6/2019 8:00	2/6/2019 2:00	CST
2/6/2019 9:00	2/6/2019 3:00	CST
2/6/2019 10:00	2/6/2019 4:00	CST
2/6/2019 11:00	2/6/2019 5:00	CST
2/6/2019 12:00	2/6/2019 6:00	CST
2/6/2019 13:00	2/6/2019 7:00	CST
2/6/2019 14:00	2/6/2019 8:00	CST
2/6/2019 15:00	2/6/2019 9:00	CST
2/6/2019 16:00	2/6/2019 10:00	CST
2/6/2019 17:00	2/6/2019 11:00	CST
2/6/2019 18:00	2/6/2019 12:00	CST
2/6/2019 19:00	2/6/2019 13:00	CST
2/6/2019 20:00	2/6/2019 14:00	CST
2/6/2019 21:00	2/6/2019 15:00	CST
2/6/2019 22:00	2/6/2019 16:00	CST
2/6/2019 23:00	2/6/2019 17:00	CST
2/7/2019 0:00	2/6/2019 18:00	CST
2/7/2019 1:00	2/6/2019 19:00	CST
2/7/2019 2:00	2/6/2019 20:00	CST
2/7/2019 3:00	2/6/2019 21:00	CST
2/7/2019 4:00	2/6/2019 22:00	CST
2/7/2019 5:00	2/5/2019 18:00	CST
2/7/2019 6:00	2/5/2019 19:00	CST
2/7/2019 7:00	2/5/2019 20:00	CST
2/7/2019 8:00	2/5/2019 21:00	CST
2/7/2019 9:00	2/5/2019 22:00	CST
2/7/2019 10:00	2/5/2019 23:00	CST
2/6/2019 0:00	2/5/2019 16:00	PST
2/6/2019 1:00	2/5/2019 17:00	PST
2/6/2019 2:00	2/5/2019 18:00	PST
2/6/2019 3:00	2/5/2019 19:00	PST
2/6/2019 4:00	2/5/2019 20:00	PST
2/6/2019 5:00	2/5/2019 21:00	PST
2/6/2019 6:00	2/5/2019 22:00	PST
2/6/2019 7:00	2/5/2019 23:00	PST
2/6/2019 8:00	2/6/2019 0:00	PST
2/6/2019 9:00	2/6/2019 1:00	PST
2/6/2019 10:00	2/6/2019 2:00	PST
2/6/2019 11:00	2/6/2019 3:00	PST
2/6/2019 12:00	2/6/2019 4:00	PST
2/6/2019 13:00	2/6/2019 5:00	PST
2/6/2019 14:00	2/6/2019 6:00	PST
2/6/2019 15:00	2/6/2019 7:00	PST
2/6/2019 16:00	2/6/2019 8:00	PST
2/6/2019 17:00	2/6/2019 9:00	PST
2/6/2019 18:00	2/6/2019 10:00	PST
2/6/2019 19:00	2/6/2019 11:00	PST
2/6/2019 20:00	2/6/2019 12:00	PST
2/6/2019 21:00	2/6/2019 13:00	PST
2/6/2019 22:00	2/6/2019 14:00	PST
2/6/2019 23:00	2/6/2019 15:00	PST
2/7/2019 0:00	2/6/2019 16:00	PST
2/7/2019 1:00	2/6/2019 17:00	PST
2/7/2019 2:00	2/6/2019 18:00	PST
2/7/2019 3:00	2/6/2019 19:00	PST
2/7/2019 4:00	2/6/2019 20:00	PST
2/7/2019 5:00	2/6/2019 21:00	PST
2/7/2019 6:00	2/6/2019 22:00	PST
2/7/2019 7:00	2/6/2019 23:00	PST
2/7/2019 8:00	2/7/2019 0:00	PST
2/7/2019 9:00	2/7/2019 1:00	PST
2/7/2019 10:00	2/7/2019 2:00	PST

The join Date-UTC to Date dimension and Flag column in slicer.

If it helps, mark it as a solution

Kudos are nice too 

[sjt003001]

Hi VasTg,

Thanks for the response.  The idea of using a bridge table is intriguing, but I'm not sure if it meets my requirement of handling all time zone reporting with a simple switch between Local and HQ.

Here is some better context regarding my data and situation.

We have stores across the US.  Stores look at their sales according to their timezone and HQ looks at everything in Eastern.  There is no need to look at everything in a different timezone than local or EST (all in Central or whatever).

We also have a date and time dimension with many attributes.  In particular, our date dimension has around 100 attributes (fiscal period, retail periods, special events, etc.).

Sample data:

Sales

Store	Sales $	Local Date	EST Date	Local Time	EST Time
Boston	100	2/7/2020	2/7/2020	1:30 AM	1:30 AM
Chicago	100	2/7/2020	2/7/2020	12:30 AM	1:30 AM
San Francisco	100	2/6/2020	2/7/2020	10:30 PM	1:30 AM

Date

The Date	Fiscal Today Flag	Fiscal Yesterday Flag
2/5/2020
2/6/2020		Y
2/7/2020	Y

A simplistic example obviously, but if users are looking at the local time and want to see total sales, it would be:

Fiscal Today:  200 (Boston and Chicago)

Fiscal Yesterday:  100 (San Francisco)

If they are looking at EST, it would be:

Fiscal Today:  300

Fiscal Yesterday:  0

In my desired solution, the user would be able to use a single Fiscal Today attribute from one dimension, then a Time Zone attribute from somewhere.  The Time Zone could be a slicer allowing Fiscal Today to switch between Local and EST.

Right now our users need to remove Fiscal Today and add another one from a different dimension.  I believe they would need to do the same with the bridge table solution.  If all the date attributes were in the bridge table, they would still need to change attributes rather than just change the time zone slicer (Fiscal Today could be one value if local and another when eastern).  If the bridge table is narrow and all the attributes are in the two date dimensions, there is still the need to switch between the date dimensions.

Thanks,

Scott

[VasTg]

@sjt003001 

Scott,

Did you look at the edit in my previous post?

We can't actually model what you are asking when you have different timezones in fact. It should be in a seperate table with two rows instead of two columns.

For you to filter based on timezone, you should connect the sample table I have on top of date dimension. You should add your fiscal flag as an additional column in my table. The timezone column should be your slicer so that they can filter either PST or EST. Once they choose EST, only the rows for EST will be filtered in the below table and that filters your data dimension and that filters the sales fact.

You have to build the table as below ans use Timezone and fiscal flag columns as slicer.. This is just an example for the approach.

Date	Timezone date	Timezone	Fiscal flag
2/7/2020 15:00	2/7/2020 10:00	EST	Y
2/7/2020 16:00	2/7/2020 11:00	EST	Y
2/6/2020 15:00	2/6/2020 10:00	EST
2/7/2020 15:00	2/7/2020 7:00	PST	Y
2/7/2020 16:00	2/7/2020 8:00	PST	Y
2/6/2020 15:00	2/6/2020 7:00	PST

If you have any questions let us know.

[VasTg · Accepted solution]

@sjt003001 

With your sample data, I was able to create what you asked with the approach I mentioned.

For the 1st screenshot..

3. Shows the input data in each table. Time_Period_flat(refer last screenshot) and Timezone are the new tables you have to build. Ignore the Today and Yesterday flag from Date dimension.

1. Shows the data for Local Time.Today and Yesterday flag comes from Time_Period_flat table and the slicer are from TimeZone table(or you could avoid Timezone table and add slicers directly from Time_period_flat).

2. Shows the data for EST.Today and Yesterday flag comes from Time_Period_flat table and the slicer are from TimeZone table(or you could avoid Timezone table and add slicers directly from Time_period_flat).

Here is the model.

Relationship

Once you build the table Time_Period_flat, you could utilize the approach without any changes to measure.

Edit:

Ignore the Time_Period_flat table data from 1st screenshot..

Refer below for complete data.

If it helps, mark it as a solution

Kudos are nice too.

[sjt003001]

Hi VasTg,

OK, I think I understand what you are doing better now.  

I was thinking you would end up with multiple time or date dimensions, but that's not the case.  You don't have anything like bi-directional ambiguity either I don't think, which is good.

I will need to adapt some before working into my model as I have separate date and time dimensions.  But I'll mark you as the solution now as it seems like the right way to go.

Thanks much!

Scott