Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Erika_S
Helper I
Helper I

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 matrixJust blank matrix

5 REPLIES 5
kaszab10
New Member

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
New Member

Did you ever find a solution to this?

CNENFRNL
Community Champion
Community Champion

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?


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

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? 

 

Relationship.PNG

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors