cancel
Showing results for 
Search instead for 
Did you mean: 
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.

View solution in original post

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.