Forum Discussion
Advocate IHow to remove Date Hierarchy
Hi,
How to remove Date Hierarchy
15 Replies
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".
This doesn't work in 2023. Can someone tell me how to remove/delete an unwanted hierarchy please? Thanks
- Johag
Advocate II
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.
cchiltonHelper I
ty this is exactly what i needed to know
Hello, I have a similar problem to that discussed in this thread.
My model contains orderTable and orderDetails tables as follows:
orderTable
orderDate
customerNumber
orderNumberorderDetails
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!!- Ashish_Mathur
Super User
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.
