Forum Discussion
Update active relationship globally
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.
Memorable MemberAnonymous
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.
Memorable MemberAnonymous
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.
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