Calendar Table Not Connecting With Direct Query

[giovanna8988]

Hello,

I am trying to relate my calendar table to a data set from a direct query, but my relationships are not matching. I am pretty certain it has to do with the fact my data set has date/time format. Unfortunately, I cannot use import and need to use direct query.

I have tried the following...

1. Utilizing SQL query to bring in the data set utilizing TO_DATE() function, but I can see somehow it is still pulling in a time.

2. Utilizing SUMMARIZE COLUMNS and SELECT COLUMNS to import the direct query data and changing the format from date/time to date.

Is there a DAX statement I can use within my summarize or select columns that will extract that date?

Thanks in advance!

[jewel_at]

Hi @giovanna8988 

Have you tried changing the data type of the date from your SELECT COLUMNS to "Date" ? Select the field >> Column Tools

Hope that helps!

Jewel

[giovanna8988]

I have. That was that is what I am mentioning in attempt number 2 above.

[jewel_at · Accepted solution]

Oh Sorry!

How about try this in your SELECT COLUMN, then change the data type to Date

Left(CONVERT('Table'[Timestamp], STRING),10)

This is given that your original date is mm/dd/yyyy hh:mm:ss

Do you have a sample of your data?

Jewel

[giovanna8988]

This worked! Thank you! Along with making sure I was using a measure function to calculate count rather than allowing PowerBI to calculate it for me.

[jewel_at]

Cool!