Union Table and date table relationship

[Erika_S]

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. : )Just blank matrix

[kaszab10]

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!

[nevilletwarner]

Did you ever find a solution to this?

[CNENFRNL]

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?

[Erika_S]

@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?

[Erika_S]

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?