Dynamically display a column based on filter selection

[DataFun]

I am trying to dynamically choose which column to display in a matrix visual.  My data has a column with a UTC Start Time (no date is included).  My users are global, and I want to be able to display the start time data in the timezone they select.  This would be the first column in the matrix.  I have created a slicer with the various timezones.  I have also created a timezone table that converts UTC into the other time zones.  What I am struggling with is how to dynamically choose the column from the timezone table  to display as the first column of the matrix.  I've looked at examples of creating a measure, but they have used a SUMX or MAX and that doesn;t make sense to me.  I just want to be able to have the user select their timezone and have the times displayed in that time zone.  For example, 8:00:00 AM UTC would be displayed as 9:00:00 AM CET if the CET time zone is selected.  My first column, should include 48 entries, one for each hour and half hour in a 24 hour period.  I would appreciate if someone can get me over the hump of how to do this.

My Data

Data for Slicer

[Anonymous]

Hi @DataFun ,

Could you please tell us that if your problem has been solved?

I think OwenAuger's latest reply can solve it. 

If so, please accept his reply as solution. More people will benefit from it because of this.

If not, please feel free to tell us.

Best Regards,
Stephen Tao

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

[OwenAuger]

Hi @DataFun 

It sounds like a field parameter that allows selection of the time zone column would be the way to go.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

You would create a field parameter including each Timezone column. You could then edit the field parameter table definition so that the name of each option is the appropriate timezone code.

One thing to watch out for is the sort order of the columns in the Timezone table itself. Would you want each column to sort by the UTC column?

Are you able to get something like this working?

[DataFun]

Hi @OwenAuger ,

I have setup the parameter and I am not getting the result I expect.  This is what my TimeZoneData looks like:

This is what my list of TimeZones look like:

This is what the DAX code for my Parameter looks like:

Notice the error.  All my time fields in the table are a type Time with format of *1:30:55 PM (Long Time). The list of time zones is a text list.  I assume this is where the error is coming in, but I can't set this to a time format.  Suggestions on how to resolve the error?

FYI - the visual I am trying to use this with is a matrix.  This is an example of the output I want, but I want the first column to update based upon the timezone the user selects:

[OwenAuger · Accepted solution]

Hi again @DataFun 

Thanks for trying this out!

Based on the error message you're getting, it looks like you may have changed some column types in the field parameter table itself - is that right? I tried changing the first column of the field parameter table to type Time and got a similar error message to yours.

The field parameter table created with the interface should end up with two text columns and an integer column, and there is no need to change its column types.

The TimeZones single-column table you mentioned shouldn't be needed in the model, since the field parameter table serves the purpose of selecting timezones.

To help figure this out, I have attached a PBIX with a similar field parameter setup using some Contoso data and a dummy time column added to the Sales table.

Field parameter table:

• The 1st column is type text and contains the "display values" for each parameter value, that the user will select from.
• The 2nd column is type text and must contain valid references to the columns that are available for selection (the NAMEOF function helps ensure this).
• The 3rd column is type integer and specifies sort order.

TimeZoneData table:

Should be similar to yours with 48 rows and a column per timezone:

Overall model diagram

I have Sales[UTC Time] related to TimeZoneData[UTC]. I'm assuming your model is similar?

Parameter Timezones (field parameter) doesn't need any relationships.

I created a simple report page approximating what you're doing.

I did notice that the header above the row header column of the matrix changes with different TimeZone selections.

If you wanted a static label, you could change the first column of the field parameter table to a constant value "Time Slot" and add another column to use on the slicer, but I didn't worry about that.

Hopefully that helps with setting up the model at your end.

Let me know how you go 🙂

[DataFun]

I have accepted this as the solution.  I appreciate you sharing this.  In my situation, we decided to go another direction.  I am going to leverage this solution in another scenario.

[DataFun]

Thank you!  I have read about Field Parameters but have not yet used one.  I will explore this and if I have any questions I will respond to your post.  If I figure it out, I will mark this as Solution Accepted.