cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thomas_p
Frequent Visitor

Using 'Week of' dates for chart Axis, Axis will not accept a calculated measure

Short time lurker, first time poster. New to Power BI.

 

My data is a direct query of a SQL view. I used DAX functions to conslidate my time stamped data to the 'Week of' starting on a Monday (thanks to other posts on this forum!). Every column starting with "ws" is a new measure.

thomas_p_0-1596055193357.png

 

I would like to use the wsWeekStartingDate as the Axis (x axis) on a column chart. When I drag the wsWeekStartingDate measure to the Axis, it will not accept it. The format for this measure is a date format.

thomas_p_2-1596056226687.png

thomas_p_3-1596056248226.png

When I use the the Time Stamp Date field from SQL it is categorized by the millisecond and shows a separate column for each data point:

thomas_p_1-1596055701005.png

Selecting a single data point shows milliseconds.

thomas_p_4-1596056452761.png

I would like the values would be 3 for the week of 7/20 and 2 for the week of 7/13.

 

Any idea why the chart Axis will not accept my new measure? Is there a different way I should go about categorizing the data by 'Week of'?

 

 

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@thomas_p , Axis will not allow a measure. So make sure you bring a date table and week start date from Database.

 

Most of the time direct query will allow mixed mode. This means you will able to create a date table in power Bi and use with the direct query. But this you have test.

 

For date table in DB

example date table coming from DB- https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

 

Power BI — WTD Questions— Time Intelligence 4–5
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

Any Weekday Week - Decoding Date and Calendar 2-5 - Power BI Turning 5 Celebration Series
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Greg_Deckler
Super User IV
Super User IV

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors