Forum Discussion

Erika_S's avatar
Erika_S
Icon for Helper I rankHelper I
5 years ago

Union Table and date table relationship

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

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Erika_S
      Icon for Helper I rankHelper I
      5 years ago

      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? 

       

       

       

    • Erika_S's avatar
      Erika_S
      Icon for Helper I rankHelper I
      5 years ago

      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?

  • kaszab10's avatar
    kaszab10
    New Member
    3 years ago

    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!