Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Update active relationship globally

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 dat...
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    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.

VasTg's avatar
VasTg
Icon for Memorable Member rankMemorable Member
6 years ago

Anonymous 

 

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

DatePST DateCST 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 - UTCCST/PSTFlag
2/6/2019 0:002/5/2019 18:00CST
2/6/2019 1:002/5/2019 19:00CST
2/6/2019 2:002/5/2019 20:00CST
2/6/2019 3:002/5/2019 21:00CST
2/6/2019 4:002/5/2019 22:00CST
2/6/2019 5:002/5/2019 23:00CST
2/6/2019 6:002/6/2019 0:00CST
2/6/2019 7:002/6/2019 1:00CST
2/6/2019 8:002/6/2019 2:00CST
2/6/2019 9:002/6/2019 3:00CST
2/6/2019 10:002/6/2019 4:00CST
2/6/2019 11:002/6/2019 5:00CST
2/6/2019 12:002/6/2019 6:00CST
2/6/2019 13:002/6/2019 7:00CST
2/6/2019 14:002/6/2019 8:00CST
2/6/2019 15:002/6/2019 9:00CST
2/6/2019 16:002/6/2019 10:00CST
2/6/2019 17:002/6/2019 11:00CST
2/6/2019 18:002/6/2019 12:00CST
2/6/2019 19:002/6/2019 13:00CST
2/6/2019 20:002/6/2019 14:00CST
2/6/2019 21:002/6/2019 15:00CST
2/6/2019 22:002/6/2019 16:00CST
2/6/2019 23:002/6/2019 17:00CST
2/7/2019 0:002/6/2019 18:00CST
2/7/2019 1:002/6/2019 19:00CST
2/7/2019 2:002/6/2019 20:00CST
2/7/2019 3:002/6/2019 21:00CST
2/7/2019 4:002/6/2019 22:00CST
2/7/2019 5:002/5/2019 18:00CST
2/7/2019 6:002/5/2019 19:00CST
2/7/2019 7:002/5/2019 20:00CST
2/7/2019 8:002/5/2019 21:00CST
2/7/2019 9:002/5/2019 22:00CST
2/7/2019 10:002/5/2019 23:00CST
2/6/2019 0:002/5/2019 16:00PST
2/6/2019 1:002/5/2019 17:00PST
2/6/2019 2:002/5/2019 18:00PST
2/6/2019 3:002/5/2019 19:00PST
2/6/2019 4:002/5/2019 20:00PST
2/6/2019 5:002/5/2019 21:00PST
2/6/2019 6:002/5/2019 22:00PST
2/6/2019 7:002/5/2019 23:00PST
2/6/2019 8:002/6/2019 0:00PST
2/6/2019 9:002/6/2019 1:00PST
2/6/2019 10:002/6/2019 2:00PST
2/6/2019 11:002/6/2019 3:00PST
2/6/2019 12:002/6/2019 4:00PST
2/6/2019 13:002/6/2019 5:00PST
2/6/2019 14:002/6/2019 6:00PST
2/6/2019 15:002/6/2019 7:00PST
2/6/2019 16:002/6/2019 8:00PST
2/6/2019 17:002/6/2019 9:00PST
2/6/2019 18:002/6/2019 10:00PST
2/6/2019 19:002/6/2019 11:00PST
2/6/2019 20:002/6/2019 12:00PST
2/6/2019 21:002/6/2019 13:00PST
2/6/2019 22:002/6/2019 14:00PST
2/6/2019 23:002/6/2019 15:00PST
2/7/2019 0:002/6/2019 16:00PST
2/7/2019 1:002/6/2019 17:00PST
2/7/2019 2:002/6/2019 18:00PST
2/7/2019 3:002/6/2019 19:00PST
2/7/2019 4:002/6/2019 20:00PST
2/7/2019 5:002/6/2019 21:00PST
2/7/2019 6:002/6/2019 22:00PST
2/7/2019 7:002/6/2019 23:00PST
2/7/2019 8:002/7/2019 0:00PST
2/7/2019 9:002/7/2019 1:00PST
2/7/2019 10:002/7/2019 2:00PST

 

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

 

 

If it helps, mark it as a solution

Kudos are nice too 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

StoreSales $Local DateEST DateLocal TimeEST Time
Boston1002/7/20202/7/20201:30 AM1:30 AM
Chicago1002/7/20202/7/202012:30 AM1:30 AM
San Francisco1002/6/20202/7/202010:30 PM1:30 AM


Date

The DateFiscal Today FlagFiscal Yesterday Flag
2/5/2020  
2/6/2020 Y
2/7/2020Y 


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