topic Re: Calendar Table Not Connecting With Direct Query in DAX Commands and Tips

Calendar Table Not Connecting With Direct Query

giovanna8988 — Tue, 03 Jan 2023 20:23:32 GMT

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!

Re: Calendar Table Not Connecting With Direct Query

jewel_at — Tue, 03 Jan 2023 21:16:20 GMT

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

Re: Calendar Table Not Connecting With Direct Query

giovanna8988 — Tue, 03 Jan 2023 21:18:55 GMT

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

Re: Calendar Table Not Connecting With Direct Query

jewel_at — Tue, 03 Jan 2023 21:29:11 GMT

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

Re: Calendar Table Not Connecting With Direct Query

giovanna8988 — Wed, 04 Jan 2023 15:18:07 GMT

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.

Re: Calendar Table Not Connecting With Direct Query

jewel_at — Wed, 04 Jan 2023 15:19:45 GMT

Cool!