Showing visual in different timezone based on slicer selection or RLS

[WHENG]

I have an Events table with the following columns

• Event ID
• Start date (Data in UTC format)
• Title
• Status
• Category

A timezone table with the following column

• Timezone

Is there a way that i can display the Table visual dynamically in different timezone? 3 scenarios below:
1. When a timezone was selected in the slicer, the Start date in the Table visual will change accordingly. OR

2. Table visual display according to the timezone based on USERPRINCIPALNAME. OR

3. By default, table visual display the correspond timezone based on USERPRINCIPALNAME, if user would like to view it in different timezone, can he change it using the slicer.
Is any of the above achievable?

[lbendlin]

#2 is something that comes for free.  The other ones are harder to accomplish - but not impossible.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.

[WHENG]

Sample Event table (in UTC)

Event ID	Start date	Title	Status	Category
I-00001	1/1/2022 8:00am	POS system hung	Closed	High
I-00002	9/1/2022 4:00pm	Door locked faulty	Pending	Medium
I-00003	10/1/2022 2:00pm	Faulty light	Pending	Low
I-00004	29/1/2022 6:00pm	POS system hung	Open	High
I-00005	29/1/2022 9:00pm	Vending machine mulfunction	Open	Low

Here is the Timezone table

Timezone

UTC

Asia/Singapore

Asia/Bangkok

2 duplicated tables were created in power query with Start date replaced according to the timezone
Asia/Singapore time zone

Event ID	Start date	Title	Status	Category
I-00001	1/1/2022 4:00pm	POS system hung	Closed	High
I-00002	10/1/2022 0:00am	Door locked faulty	Pending	Medium
I-00003	10/1/2022 10:00pm	Faulty light	Pending	Low
I-00004	30/1/2022 2:00am	POS system hung	Open	High
I-00005	30/1/2022 5:00am	Vending machine mulfunction	Open	Low

Asia/Bangkok timezone

Event ID	Start date	Title	Status	Category
I-00001	1/1/2022 3:00pm	POS system hung	Closed	High
I-00002	9/1/2022 11:00pm	Door locked faulty	Pending	Medium
I-00003	10/1/2022 9:00pm	Faulty light	Pending	Low
I-00004	30/1/2022 1:00am	POS system hung	Open	High
I-00005	30/1/2022 4:00am	Vending machine mulfunction	Open	Low

With some example online (Change the Column or Measure Value in a Power BI Visual by Selection of the Slicer: Parameter Table ...), i was able to achieve some result

However, table did changed according to the slicer but the "Start date" is always the same. Cause the DAX for the selected slicer option can only return a single value. Not sure what function to use in place of MAX()

Start date =

SWITCH(

SELECTEDVALUE(Timezone[Timezone]),

"Asia/Singapore", CALCULATE(MAX(SGT[Start date])),

"Asia/Bangkok", CALCULATE(MAX(THT[Start date])),

CALCULATE(MAX(UTC[Start date]))

)

Any idea if it's possible to change the chart visual (e.g line or bar) as well according to the selected Timezone too?

For item 2. I was thinking adding a new column (timezone) to the tables and merging them into one. But it wont work cause other than the "Start date" rest of the value is identical. Since it's not a single table, i not sure how to achieve RLS

[v-chenwuz-msft]

Hi @WHENG ,

The place of the max() is the same as selectedvalue(). But max() return the maxnium date in the date column of current row Event ID, For example, event id is I-0001 and it has one rows in events table, which row's date has one value 1/1/2022 4:00pm, so the max() date is 1/1/2022 4:00pm, it returns 1/1/2022 4:00pm. But if this event-id has two rows, it will return only one of them.

change the chart visual (e.g line or bar) as well according to the selected Timezone too

Please provide the measure of count. Or some measure like this:

measure =
VAR _switchtime =
    SWITCH( TRUE(), "Asia/Singapore", 8, "Asia/Bangkok", 7, 0 )
RETURN
    COUNTROWS(
        FILTER(
            'event table',
            ( [start date] - _switchtime )
                <= MAX( calendar[date] )
                && ( [start date] - _switchtime )
                    >= MIN( calendar[date] )
        )
    )

item 3# RLS:

get the default timezero =
VAR _d =
    CALCULATE(
        MAX( 'usertimezero' ),
        FILTER( usertimezero, [useremail] = USERPRINCIPALNAME() )
    )
VAR _a =
    SWITCH(
        _d,
        "Asia/Singapore", CALCULATE( MAX( SGT[Start date] ) ),
        "Asia/Bangkok", CALCULATE( MAX( THT[Start date] ) ),
        CALCULATE( MAX( UTC[Start date] ) )
    )
RETURN
    IF( SELECTEDVALUE( Timezone[Timezone] ) = BLANK(), _a, [Start date] )

if you need more help , you can share pbix file without sensitive data.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WHENG]

Hi @v-chenwuz-msft,

Please find the pbix file below
https://drive.google.com/file/d/1BLrhQNP5FSHdLfnqae5eSBCQcpUFXr9d/view?usp=sharing

I got this error on the measure

I couldn't use measure as the axis for the chart visual (e.g line or bar).

Another way i can do is using bookmark to show the selected timezone chart. But it will become hard to manage if more timezone added. Bookmark doesn't change according to slicer as well

Appreciate your advice

Thank you

[v-chenwuz-msft · Accepted solution]

Hi @WHENG ,

This code to change the bar chart.

Bar chart =
VAR _datetime =
    SWITCH(
        SELECTEDVALUE( Timezone[Timezone] ),
        "Asia/Singapore", TIME( 8, 0, 0 ),
        "Asia/Bangkok", TIME( 7, 0, 0 ),
        0
    )
RETURN
    COUNTROWS(
        FILTER(
            UTC,
            DATE( YEAR( [Start date UTC] + _datetime ), MONTH( [Start date UTC] + _datetime ), DAY( [Start date UTC] + _datetime ) )
                >= MIN( 'Calendar'[Date] )
                && DATE( YEAR( [Start date UTC] + _datetime ), MONTH( [Start date UTC] + _datetime ), DAY( [Start date UTC] + _datetime ) )
                    <= MAX( 'Calendar'[Date] )
        )
    )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WHENG]

Hi @v-chenwuz-msft,

When a date slicer was added to filter the data (considering more data are added in the future). the table and chart doesn't show correctly anymore. 
A relationship between the Calendar[Date] and UTC[Start date UTC] is breaking the table/chart too

https://drive.google.com/file/d/1eRFOdOpQRkWKrB2EX26IvRwpkO5bgV69/view?usp=sharing

Your advice would be much appreciated

Thank you

[WHENG]

Hi v-chenwuz-msft,

Thanks a lot!