Forum Discussion
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.
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.
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:
Selecting a single data point shows milliseconds.
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
- Greg_Deckler
Community Champion
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/ba-p/279563
- amitchandak
Super User
Anonymous , 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-Intelligence-in/ba-p/922885
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-Last-Week/ba-p/1051123Any 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-Power-BI-Turning/ba-p/1187482
