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
ashishtele
Frequent Visitor

How to create the measure based on Date column in Excel

Hi,

 

I have Date column in excel. I imported it in Power BI desktop and try to use it in Date DAX formulas like

 

Weekday = weekday(table[Date],2)

 

but I am not able to do so. I am not able to use Date column in measure calculations.

Can you please let me know the reason ?

1 ACCEPTED SOLUTION
greggyb
Resident Rockstar
Resident Rockstar

WEEKDAY() demands a scalar value. You are passing it a column reference. When you ask for WEEKDAY( 'DimDate'[Date] ), you're asking the formula engine to return the weekday of a list of dates? How can it return a single weekday for >1 dates?

 

What is the purpose of this measure? Typically weekday would be an additional field in the date table, not a measure. You can add a calculated column in your date dimension using the same formula you've proposed. This is because calculated columns are evaluated in row context. In a row context, any column is guaranteed to have one value.

View solution in original post

5 REPLIES 5
greggyb
Resident Rockstar
Resident Rockstar

WEEKDAY() demands a scalar value. You are passing it a column reference. When you ask for WEEKDAY( 'DimDate'[Date] ), you're asking the formula engine to return the weekday of a list of dates? How can it return a single weekday for >1 dates?

 

What is the purpose of this measure? Typically weekday would be an additional field in the date table, not a measure. You can add a calculated column in your date dimension using the same formula you've proposed. This is because calculated columns are evaluated in row context. In a row context, any column is guaranteed to have one value.

Hi,

 

Thanks for this explanation. I was trying measure calculation on row level context.

Would these additional derived columns affect the performace as I need to calculate few interdependent columns ?

 

Thank you in advance

@ashishtele It looks like your confusing measures and calculated columns. Read through this for a detailed explanation of both and when/how to use them.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer...Thank you. It was good to read the article.

I still haven't got a clue what it is you want to do.

 

Do calculated columns affect performance? Yes. Do they affect it enough to worry about? Almost definitely not.

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.