topic Re: Union Table and date table relationship in DAX Commands and Tips

Union Table and date table relationship

Erika_S — Thu, 24 Sep 2020 09:08:53 GMT

Hi guys,

I hope maybe you can help me.

I created a Dax dates table. Like this: Dates = ADDCOLUMNS( CALENDAR (DATE(YEAR(MIN('Looker Union'[Creation period datetime])),1,1), DATE(YEAR(MAX('Looker Union'[Closing period datetime])),12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), etc. I marked it as a Date table.

Then i also created a Union Table made out of two same tables. I Hide from View the other two tables and using only Union now. Then I created a relationship on dates - that seemed fine. BUT when i try to create any other dax results come back blank in the date column when i check in matrix. attached the screenshot.

DAX: Same Period LY = CALCULATE([Total tickets], SAMEPERIODLASTYEAR('Dates'[Date]))

Can you see anything i might be doing wrong? Any tips or advice would be really appreacited. : )

Re: Union Table and date table relationship

CNENFRNL — Thu, 24 Sep 2020 09:32:36 GMT

Hi, Erika_S , without a glance at your whole data model, it's hard to pinpoint the cause of issue. An attachment of mockup pbix file surely helps a lot.

Is "YearQuarter" a column from Dates table? Any other filters (directly/indirectly) affect this viz?

Re: Union Table and date table relationship

Erika_S — Thu, 24 Sep 2020 09:42:30 GMT

Yes, YearQuarter from Dates table. There is literatly nothing else going on (no filters, no other dax (yet) 🙂 )

If needed ill create a mock up file as well.

But the Dax for same period looks correct, right?

Re: Union Table and date table relationship

Erika_S — Thu, 24 Sep 2020 09:50:06 GMT

CNENFRNL When i do the same dax for same period last year- for one of the tables that I used in the Union, all is fine, returns the correct results, no problem. But for some reason Unioned table just does work. Maybe there are issues doing dax between dates table and unioned tables that i am not aware off?

Re: Union Table and date table relationship

nevilletwarner — Fri, 05 Mar 2021 00:52:52 GMT

Did you ever find a solution to this?

Re: Union Table and date table relationship

kaszab10 — Wed, 18 Jan 2023 21:03:05 GMT

Hi Erika_S ,

I had a very similiar problem in the last days. I have an unioned date table and a dataset tablet. I created a Many-Many connection between them and this filter helped me solve it.

In your example:

Same Period LY = CALCULATE([Total tickets], ALLCROSSFILTERED('Dates'), SAMEPERIODLASTYEAR('Dates'[Date]))

I hope it can help for you as well!