topic Re: Fabric - date dimension and hierarchies in Data Warehouse

Fabric - date dimension and hierarchies

joakimfenno — Thu, 26 Sep 2024 11:40:35 GMT

I have requirements to have a date hierarchy to use in visualizations (linear graph etc.)
what the user needs is a graph with for example months on the x axis label but that has values on a day level
adding the days to the axis makes the graph too "long"
usually (analysis services, power bi etc:9 you get a date hierarchy by default from your date table (like in the screemshot below)
This works fine for me when I import the data to power bi from fabric warehouse however it does not work using semantic model

I read that this is a limitation for direct lake. However - is there a way to get this to work in the semantic model - can I set another storage mode for my date table? other workarounds?

I know that I can create hierarchies manually in the semantic model but I dont get this nice behaviour with the default date hierarchy that I need

Re: Fabric - date dimension and hierarchies

lbendlin — Thu, 26 Sep 2024 15:40:45 GMT

Don't use a hierarchy at all. Use the Date column and let the visual create the X axis hierarchy automatically when you choose a "continuous" axis type.

Re: Fabric - date dimension and hierarchies

joakimfenno — Thu, 26 Sep 2024 16:58:16 GMT

that only gives me data points for the date/day?

Re: Fabric - date dimension and hierarchies

lbendlin — Thu, 26 Sep 2024 17:02:08 GMT

In a line chart visual (for example) the x axis will auto scale to fit the data.

Re: Fabric - date dimension and hierarchies

joakimfenno — Thu, 26 Sep 2024 18:21:42 GMT

it do not seem to work for me

semantic model

source = warehouse table

tagged as date table

date column od date type (in warehouse and semantic model)

I have fact/transaction values hourly

Re: Fabric - date dimension and hierarchies

lbendlin — Thu, 26 Sep 2024 21:07:19 GMT

I have fact/transaction values hourly

Not sure what that means.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Fabric - date dimension and hierarchies

Anonymous — Fri, 27 Sep 2024 06:17:07 GMT

Hi joakimfenno ,

Thanks for the reply from lbendlin .

I understand your frustration with the date hierarchy working fine when connecting to warehouse but not when connecting to semantic model.

I was able to reproduce your error.

Connect to warehouse:

Connect to semantic model:

The storage mode cannot be changed:

As an alternative, you can create hierarchies manually. Make sure your table has four columns: year, quarter, month, and day, and drag these four columns into the hierarchy in turn after selecting New Hierarchy. You can see that the hierarchy I created manually is no different from the default hierarchy.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Fabric - date dimension and hierarchies

joakimfenno — Fri, 27 Sep 2024 09:19:13 GMT

teh issue I have is that year, quarter, month etc are not identified as "time" attributes, therefore these fields show up om the x-axis

my requirements are

continuous x-axis (i need to see day level values for y-axis using month level for x-axis for example)

hierarchy to drill up/down in graph

Re: Fabric - date dimension and hierarchies

Anonymous — Mon, 07 Oct 2024 05:40:44 GMT

Hi joakimfenno ,

Are your year, quarter, month, etc. attributes separate fields? Do you have a Date field (something in the format of YYYYMMDD).

When I was testing the connection semantic model, the Date field wasn't correctly recognized as a date format either, but I was able to get it to work by creating the hierarchy manually.

I don't quite understand this need of yours: “i need to see day level values for y-axis using month level for x-axis for example”. Shouldn't month level and day level represent time on the same axis?

Best Regards,
Yang
Community Support Team

Re: Fabric - date dimension and hierarchies

joakimfenno — Mon, 07 Oct 2024 07:50:56 GMT

I have one source column for date on YYYY-MM-DD format.

I also have year and month columns and I am able to manually create a hierarchy (year-month-date).

What I want is to have a continous x-axis and be able to frill down down/up. With my hierarchy I see one value (axis lable) per year, month and date. I want to be absole to look at monts but see all values (date) if I move along the axis.

Are you following? I will create some screenshots otherwise

Re: Fabric - date dimension and hierarchies

lbendlin — Mon, 07 Oct 2024 12:31:08 GMT

All you need to do is put the date field on the x axis (and nothing else). The visual will automatically create the hierarchy labels on the x axis.

the date field needs to have the actual date format, not a string.

Re: Fabric - date dimension and hierarchies

joakimfenno — Tue, 08 Oct 2024 09:16:24 GMT

It does not work for me

This is what I get (screenshot).

No hierarchy (drill down/up) and I dont see months on x-axis

Re: Fabric - date dimension and hierarchies

lbendlin — Tue, 08 Oct 2024 10:46:46 GMT

Not enough data. Include multiple months of data.

Re: Fabric - date dimension and hierarchies

A_monged — Fri, 03 Jan 2025 12:21:04 GMT

actually I can't create Hirarichy manually when I am using the semantic model from fabric and direct query