Fabric - date dimension and hierarchies

[joakimfenno]

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

[Anonymous]

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!

[A_monged]

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

[joakimfenno]

Hi

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

[Anonymous]

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

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!

[joakimfenno]

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

[lbendlin]

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.

[joakimfenno]

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

[lbendlin]

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

[joakimfenno]

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

[lbendlin]

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-...

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/1447...

[lbendlin]

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.

[joakimfenno]

 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

[lbendlin]

Not enough data. Include multiple months of data.