Forum Discussion
How to remove Date Hierarchy
- Anonymous7 years ago
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.