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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kressb
Helper V
Helper V

Cardinality Issue with DateDimension - what are other options?

Hello,

 

I have a large PowerBI Build - 50 tables, all related by a DateDimension and a Site Key.

Now I have a piece of data I need to add, but it's a bit unusual. It goes by Day of Week/# for the month  - ex: 1st Monday in the month, 2nd Monday in the Month, so on.

I wrote equations into the date dimension to automatically categorize each date as such.. the problem is, it presents cardinality issues to relate it to the DateDimension in this manner:

kressb_0-1628869946368.png

Anyone have any advice - links they can share/anything to get around this issue?

This is a manually kept file and the only thing I can think of is to .. write the #s down for every day of the year? That's not feasible..

TYIA!

1 ACCEPTED SOLUTION

@amitchandak @CNENFRNL 
Ok I have added columns for month and year.

Could someone please explain how I make that into a date?

I tried: lookupvalue(DateDimension[Date],DateDimension[Month],NewTable[Month],DateDimension[Year], NewTable[Year],DateDimension[DayInstance],NewTable[DayInstance]) 
and it returned blank..

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@kressb , My approach would be to convert these Mondays to dates

 

Do you have month and year there in that table, I can suggest how to get the date

@amitchandak @CNENFRNL 
Ok I have added columns for month and year.

Could someone please explain how I make that into a date?

I tried: lookupvalue(DateDimension[Date],DateDimension[Month],NewTable[Month],DateDimension[Year], NewTable[Year],DateDimension[DayInstance],NewTable[DayInstance]) 
and it returned blank..

@amitchandak I have month and year in the DateDimension; I don't have it in the NewTable.

I can add it if needed.

CNENFRNL
Community Champion
Community Champion

To convert wierd "Day of Week/# for the month" into normal date, it's more than needed but necessary for data model in your scenario.


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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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