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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
josef78
Memorable Member
Memorable Member

Time Intelligence Date Hierarchy on selected columns

Hi,

I have question, is possible turn on or off automatic Date hierarchy on selected date columns?

I have large fact table with many date columns, if there are date hierarchies on every date collumn (including hidden) it is performance killer, and repot is not usable. I know about option to turn off time intelligence for whole report, but it is also not solution, because manually creating hierarchy on every column, where I want see hierarchy, is time expensive, and also  not avaliable some time related funcitons.

And second question, is possible some customization on this automatic date hierarchy, I mean change lables, add week hierarchy and so on.

 

Thnaks,

J.

11 REPLIES 11
josef78
Memorable Member
Memorable Member

Any other idea?

mahoneypat
Employee
Employee

If you add a Date table to your model (and turn off Auto Date/Time, which you definitely should do), you should only need to create a single Year/Quarter/Month hierarchy that you could use in all your visuals.  With multiple date columns, you'll need to have multiple relationships between a table and the Date table and to use the USERELATIONSHIP function in your measures.  It may be some extra work now to update your model/report with that approach, but it well worth doing/learning long term.

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


I agree with you, but in my case i am handling a PBI Self Service Project, where  we define the Data Set and the users will create their own reports from it. The main problem, i am handling more than one fact table and date attributes/columns variety in each one. And even if I create the innactive relationships you mentioned , how  could Users create a basic Graph of lines, with month and years of one Date attribute with inactive relationship with Date table ?

Yes, I know, I can turn off Time Intelligence, and can build Date Hierarchies manualy. But this no solution, this is "No Time Inteligence".

 

There is more way how to build...

-You can use passive relationship to fictive role-playing date table, and then use userelationship in DAX, but this not add any hieararchy to list of columns, is not possible to directly use for visualisation, and for end users is unusable.

-You can build caluclated date table with hierarchies as you want, make copies of table many as you want (manually, or hack bim/pbix) , and make relationship each column to one table. It is best current solution. It is excactly same what doing Time Intelligence automatically, but you customize and use it only where you need. But it is very time expensive to build, and very hard to maintenance, and additionally, from user perspecitive hierarchy is shown on separeted table not under date column.

-You can calculate hierarchy members, and make hierarchy directly on fact table, but it is also time expensive, and also with negative impact to performance.

 

But any of them, and any else what I know, cannot replace Date hierarchies created by built-in Time hierarchies. But (I think) there now way how customize it. I mean, Date Hierarchies created by Time Intelligence are very good, but problem is that I cannot selective on/off, and cannot be customized.

I think we are misunderstanding each other perhaps.  You can still leverage Time Intelligence with the approach I proposed.

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


How? How I can use USERELATIONSHIP function to create Time Intelligence Date Hierarchy?

 

Update: Maybe misunderstand me, I don't need Time Intelligence "functions" for measure calculation, It is easy. I need customized "Date Hierarchy" (or hierarchies) on multiple and wanted date columns.

You create a single Date Hierarchy on your Date table, and then make a relationship from your Date[Date] column to each of your desired Date Column in your other table(s).  Only one relationship can be active at a time, so you use USERELATIONSHIP to activate an inactive relationship for a certain calculation.  Please see the link below.  With approach, you can use a shared Date hierarchy and have measures the use different date columns (e.g., number started, number finished in the date range in scope).

How To Work With Multiple Dates In Power BI | Enterprise DNA

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


No, it not solving the problem, I need customized "Date Hierarchy" for selected date "columns", which users can use in visualisation.

 

Be sure I don't want create thousands measures, for each combination of date column and real mesure. 

Well, I think, there is no way how selective turn on/off Date Hierarchies now. I created idea for it:

https://ideas.powerbi.com/ideas/idea/?ideaid=db5aec7a-3a69-eb11-8fed-281878de77da

 

Tahreem24
Super User
Super User

Try to make unwanted date hierarchy column to Text data type. 

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Thanks,

Yes, it works, but it is not solution. I need still work with this columns as date in formulas. Also changing to text bumps size in memory, and slows report.

 

I also try hide this columns (I don't need in visualisation, I don't need huge hierarchy, but I need as date type for another DAX calculations), but PBI still creating unwanted hierarchy, with no reason.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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