Forum Discussion

WHENG's avatar
WHENG
Frequent Visitor
4 years ago
Solved

Showing visual in different timezone based on slicer selection or RLS

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?

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    #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's avatar
      WHENG
      Frequent Visitor
      4 years ago

      Sample Event table (in UTC)

      Event IDStart dateTitleStatusCategory
      I-000011/1/2022 8:00amPOS system hungClosedHigh
      I-000029/1/2022 4:00pmDoor locked faultyPendingMedium
      I-0000310/1/2022 2:00pmFaulty lightPendingLow
      I-0000429/1/2022 6:00pmPOS system hungOpenHigh
      I-0000529/1/2022 9:00pmVending machine mulfunctionOpenLow


      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 IDStart dateTitleStatusCategory
      I-000011/1/2022 4:00pmPOS system hungClosedHigh
      I-0000210/1/2022 0:00amDoor locked faultyPendingMedium
      I-0000310/1/2022 10:00pmFaulty lightPendingLow
      I-0000430/1/2022 2:00amPOS system hungOpenHigh
      I-0000530/1/2022 5:00amVending machine mulfunctionOpenLow


      Asia/Bangkok timezone

      Event IDStart dateTitleStatusCategory
      I-000011/1/2022 3:00pmPOS system hungClosedHigh
      I-000029/1/2022 11:00pmDoor locked faultyPendingMedium
      I-0000310/1/2022 9:00pmFaulty lightPendingLow
      I-0000430/1/2022 1:00amPOS system hungOpenHigh
      I-0000530/1/2022 4:00amVending machine mulfunctionOpenLow


      With some example online (Change the Column or Measure Value in a Power BI Visual by Selection of the Slicer: Parameter Table Pattern - RADACAD), 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's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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.