Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JCrilly
Frequent Visitor

Date Hierarchy for Quick Measure Not Working

I defined a date hierarchy but the option appears greyed out and when i try to drag the columns over nothing happens..

QuickMeasure.PNG

 

 

10 REPLIES 10
dtg87
Frequent Visitor

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

 

ropebender
Frequent Visitor

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?

Anonymous
Not applicable

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)

Brysonds
Helper III
Helper III

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):

 

Date Field.JPG       

 

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):

 

Date Hierarchy.JPG

 

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

QuickMeasure2.PNG

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

 

PBIVersion.PNGPBIMenu.jpg

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

 

SSASDateDef.PNG

Hi @JCrilly,

Based on my test, the Date column could work on my side:

1.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.