Forum Discussion
Dynamically display a column based on filter selection
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 🙂
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:
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 🙂
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.