Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi @lavdeep
Go to File --> Options and Settings --> Options --> Current File -> Data Load
and turn off 'Auto Date/Time' (under Time Intelligence)
Sorry wrong advise.... actually when you drag it into values, you can right click and select the date name instead of the Date Hierarchy - then it will display as a single date.
If you turn the auto date stuff off.. you would then have to have your own date tables.
Hello, I have a similar problem to that discussed in this thread.
My model contains orderTable and orderDetails tables as follows:
orderTable
orderDate
customerNumber
orderNumber
orderDetails
orderNumber
lineItem
quantityOrdered
priceEach
Power BI has imported these tables however the orderDate column was imported as a hierarchy. I use the hierarchy in a few visualization and DAX some code for visualizations. The problem that I have is when I try to work with the date column as a single column using DAX. The column is date or date/time format, no aggregation, etc... just a standard date column.
For example, I want to calculate sales per week. In order to simplify how this is plotted, I convert the weeks into numbers so the resulting visual will be properly ordered. This is very similar to converting month to month number as used in monthly visuals.
This code does not work:
WEEKNUM('orderTable'[orderDate])
I receive the dreaded error "the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Therefore Power BI suggests that I use some aggregation function, which I do not wish to use.
WEEKNUM(MIN('orderTable'[orderDate]))
is an example that uses MIN to aggregate the date column to a single value and as a result I would not receive the error. Of course, this code is not helpful.
I have tried to work around this error (unsuccessfully) by creating a new temporary table. Regardless, I still run into the same error when I work with the date. I appreciate any suggestions that could help me (based upon what I read here) use DAX to either disable time intelligence on a case-by-case basis or use DAX to force my date column to be used as a date rather than a hierarchy.
Thank you!!
Hi,
Use that as a calculated column formula.
Perfect! Thank you @Ashish_Mathur for your response.
Just to summarize for anyone who may come across this same issue...
My goal was to calculate sales per week. I was able to calculate sales per week using the following formula:
WeeklySales = SUMX('orderDetails',[quantitySold] * [priceEach] ) // row-by-row sales
This line of code generates row-by-row sales however it does not sum the sales for each week. In order to sum sales for each week, I need to define a week. This is where the WEEKNUM() function is used.
The challenges that I had were that 1) My data was imported as a hierarchy and 2) I was using WEEKNUM() within a measure therefore the date hierarchy was passed as an argument. Using @Ashish_Mathur suggestion to create a calculated column the entire date (not hierarchy) is passed as an argument.
At a minimum create a calculated column like this:
OrderWeekNo = WEEKNUM([orderDate]) // add this as calculated column, NOT MEASURE
// Select the column and "Don't Summarize" in the
// summarize option otherwise it returns 1 number.
// DO NOT convert this new column to a date. Use Integer
However if you use this simple formula then weeks are aggregated for each year. For example week 1 sum would be the sum of each week 1 from 2003 + 2004 + 2005.... This can be helpful if you want to understand the sum of performance for week 1 for each year. It's not what I wanted because I wanted to see a nice visual line chart with performance for each week.
Using the measure and column code I was able to modify in the visual itself by adding the year from the hierarchy ([orderDate].[year]), OrderWeekNo, and then product of the quantityOrdered and priceEach columns to the visual. This allowed me to separate the calculated $$ by the week number and keep each year separate without converting dates to strings.
Read this post.
Now I have one additional question @Ashish_Mathur because I like to dig deeper into challenges like this. My question is WHY? Why does the weekly date need to be a column rather than a measure?
In my opinion this is duplicating an existing column from the original data. The DAX documentation for WEEKNUM() does state that it applies to a Measure. WEEKNUM()
If you have a moment, kindly help me to understand why I would need a column? What is the tradeoff of adding an additional column?
You are welcome. Measures are generally used when you want dynamic results based on slicer/filter selections. Weeknum does not have to be a dynamic solution. No matter what is selected in the slicer/filter, the weeknum of that date will remain the same.
This doesn't work in 2023. Can someone tell me how to remove/delete an unwanted hierarchy please? Thanks
If you have On-object interaction active it works in the same way as before, just in a different place.
Click on the Visual icon, go to column you want to change and click on the arrow, then chose column value or Date Hierarchy.
I have disabled 'Auto Date/Time' in setup and reloaded Power BI but cannot get rid of the hiearchy. How do you really do this?
Without reuploading the data, you can do it in the visual itself:
ty this is exactly what i needed to know
From the icon it looks like you created it by mistake. Just right click and select delete.
Sorry wrong advise.... actually when you drag it into values, you can right click and select the date name instead of the Date Hierarchy - then it will display as a single date.
If you turn the auto date stuff off.. you would then have to have your own date tables.
Hi @lavdeep
Go to File --> Options and Settings --> Options --> Current File -> Data Load
and turn off 'Auto Date/Time' (under Time Intelligence)
Disabling time intelligence disables ALL hierarchies in the file. If you need to keep some of them and get rid of one right click on the field (Visualizations/Columns), check it and uncheck "Data Hierarchy".
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
99 | |
39 | |
30 |