How to retrieve js Date from a date column in DataViewTable

[Anonymous]

Hello all,  

We have a dataViewTable that contains one measure and one date column as below : 

When we go through the columns of the table, we see that we have one column for year with type text and value "CY2017": 

We have another column for Quarter with type text and values : ["CY2017 Q1", "CY2017 Q2", "CY2017 Q3", "CY2017 Q4"]

Another column for Month , also type text with values : ["2017 Jan", "2017 Feb", "2017 Mar", "2017 Apr", "2017 May", "2017 Jun", "2017 Jul", "2017 Aug", "2017 Sep", "2017 Oct", "2017 Nov", "2017 Dec"]

This yields the following data table : 

We want our custom visual to identify each individual date from the "Calendar" dimension.

• Is dataViewTable the correct dataViewMapping to get these dates (if possible) ? 
• Why don't  the year/quarter/month columns have type dateTime as "true" ? when is dateTime ever "true" ?

Thanks in advance !

[Anonymous]

@dm-p would you mind shedding some light on this issue? 

[dm-p · Accepted solution]

Your hierarchy isn't visible in your screenshot's fields pane, but at a guess, your hierarchy in the visual contains levels for Year, Quarter and Month, but not the actual date?

If so, you would need to ensure that this is included. If you're using auto date/time, then you could change the hierarchy to use the underlying date column instead in the visual.

If your supplied column is a date/time, according to the data model, then your type will flag accordingly, e.g.:

Do you get the same output in a table visual? I've set up a similar hierarchy and this is what I get (which is what I'd expect):

If you add and actual Date column to your hierarchy then the column and type should be visible, e.g.:.

This is always a good test if you're using the table data view mapping - it it doesn't look right here, it won't look right in your visual's data view.

Also, you can't guarantee that your end users will be using a date hierarchy; most will put in a raw date. I'm not sure if this is an important requirement for your visual but you will need to ensure to specify this in any documentation for your end users if so.

Daniel

[Anonymous]

Thanks a lot @dm-p!
It seems then like we're faced with a trade-off: either use the date hierarchy and its automatic aggregations but lose the type info and actual dates or use the underlying date column but get only the lowest level of the hierarchy... Is there any way to get both? like, know from a hierarchy column that it is the result of organizing in a hierarchy what was originally a date column and get the actual date?

[dm-p]

As date hierarchies can be entirely contextual to a data model (if used at all), there's no generic solution to this. If your visual needs specific portions of a date to support a hierachy, you are best guiding the user to add the columns that the visual requires for the correct level of grain in the data view, so that they can create them in their model if they are not present and then add them to the visual.
The only way I'm aware of to get everything would require some knowledge from the end-user to make the dataset work how you intend. One of the following would be possible:

• Ensure user adds all columns from the hierarchy rather than the hierarchy itself, so that each distinct column is present in the data view for the data role. If you specifically need all parts, then I would suggest creating data roles for each part of the hierarchy you require to make sure that the user supplies them all.
• Add drill-down support for the data role and ensure the user has expanded the visual all the way down to the lowest level.

Either one of these would change the grain of the data view to the lowest possible level so you would need to aggregate them yourself in your ViewModel (but I guess you might already be doing that). You may also need to work out how to resolve the data view for your visual if the user doesn't supply what you intend, so that they can be guided accordingly.

[Anonymous]

Hi @dm-p ,

Thank you very much for the response, the solution serves us to get the date object ! 

[v-lionel-msft]

Hi @Anonymous ,

Understand data view mapping in Power BI visuals in Power BI embedded analytics for better embedded BI insights - Power BI | Microsoft Docs

Maybe you can refer to the link.

Best regards,
Lionel Chen

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

[Anonymous]

Hi @v-lionel-msft , thanks for your reply. 

Checked the reference provided, but could not see any topic that would make available datatype informations or somehow detect or define the "date" values. Please correct me if i am wrong or let me know related part on the reference . 

Thanks