How to remove Date Hierarchy

[lavdeep]

Hi,

How to remove Date Hierarchy

[AlB · Accepted solution]

Hi @lavdeep 

Go to  File --> Options and Settings --> Options --> Current File -> Data Load 

and turn  off 'Auto Date/Time' (under Time Intelligence)

[MSFTTrainUser]

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!!

[Ashish_Mathur]

Hi,

Use that as a calculated column formula.

[MSFTTrainUser]

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. 

[MSFTTrainUser]

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?  

[Ashish_Mathur]

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.

[MSFTTrainUser]

Thank you again @Ashish_Mathur for your time and attention!

[JPVent]

This doesn't work in 2023.  Can someone tell me how to remove/delete an unwanted hierarchy please?  Thanks

[Johag]

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. 

[MDKCLIRResearch]

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?

[Anonymous]

Without reuploading the data, you can do it in the visual itself:

[cchilton]

ty this is exactly what i needed to know 

[synergised]

From the icon it looks like you created it by mistake.  Just right click and select delete.

[synergised · Accepted solution]

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.

[AlB · Accepted solution]

Hi @lavdeep 

Go to  File --> Options and Settings --> Options --> Current File -> Data Load 

and turn  off 'Auto Date/Time' (under Time Intelligence)

[Hernansio]

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".