Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I defined a date hierarchy but the option appears greyed out and when i try to drag the columns over nothing happens..
Hi,
I am also having the same problem when attempting to use a Custom Date table in SSAS. The table is marked as a Date Table. However the hierarchy is never there.
Is there any update on this?
Thanks
When I try to use te Quick Measure list, it does not include any Time Intelligence category at all. Do you have to have te PRO version in otrder to get te full list of Quick Measures?
I run into similar problem with the same setting (data source = SSAS or AAS), but it's more general.
In previous version of Power BI, I could drag any Date field, and it will expand it into Date hierarchy by default when I use Table visualization.
In the latest version (October 2018), even after enabling Show dates as a hierarchy in the field list option (https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-january-2018-feature-summary/#dateHierarch...), the Date hierarchy doesn't show up at all for SSAS/AAS data source. (The setting only works for CSV data source, though)
Hi!
I think I see the source of confusion here.
You created an 'artificial' hierarchy from the field menu using some fields.
You actually have to set the property of the field in the query mode to "Date" and Power BI will create the date hierarchy for you.
Notice the icon of the field shows a calendar (indicating it's now a date field):
When you add a date field to your table, matrix, etc. It will show the 'hierarchy' by default like this (tip: click the dropdown arror to change back to actual date instead of hierarchy):
Once you use a date field, you should be able to use the time-intelligence in the quick measure.
Thanks,
I am using Analysis Services Tabular as the datasource.
The column is defined as a date in the model however power BI is not recognizing it
Can you show a picture of your date column in the query view like I did? Is it formatted to date? Is it the full date (mm/dd/yyyy)? Dending on the date format, you may have to create a separate "date table" in Power BI and set-up a relationship betwen your date column and the date table, then use the date table in your visuals/reporting.
You can create a date table in Powe BI by going to the "Modeling" tab in the ribbon, selectin "New Table" and pasting this code in. Hope this helps.
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))
When you use SSAS you don't have access to the underlying query..
version is correct
I am not talking about the SSAS query, I am talking about the query in Power BI. If you are importing data from a data source into Power BI, it automatically creates a query where you can apply ETL.
If you aren't importing the data and instead are doing a live connection, then I am afraid there isn't anything I can help with.
Yes, its a live connection
here is the date definition in SSAS tabular
Hi @JCrilly,
Based on my test, the Date column could work on my side:
Could you please check the version of the Power BI Desktop(2.63.3272.40262 64-bit (October 2018)) and test it again?
Regards,
Daniel He
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |