Hi I built a semantic model in fabric on Data Lake, I have a date table for which I was expecting to see Date hierarchy but I don't see one and I don't have an option to add hierarchy in on the table , is this not possible ? Thanks Suresh

Hi venkatasuresh_g As mentioned hierarchies are not currently supported in DirectLake

Hierarchies in Tables when using Direct Lake mode

9 Replies

GilbertQ
Super User
2 years ago
Hi venkatasuresh_g

Unfortunately, it is not possible to create hierarchies and use them in direct lake mode as an alternative, you can drag in the individual fields which will then automatically create the hierarchy for you.
venkatasuresh_g
Helper IV
2 years ago
Hi GilbertQ , Thanks for your response , When I drag year and Quarter to Y Axis it does show Hierarchy but the problem is at Quarter level it does merge Querter from all years together

Data I have

Visual

Formatting in Visualization

you can see Q1 it added 2023 and 2024 together
- GilbertQ
  Super User
  2 years ago
  Hi venkatasuresh_g
  
  Could you make sure that the Year column default setting is set to None and not to SUM?
  - venkatasuresh_g
    Helper IV
    2 years ago
    Hi GilbertQ ,
    
    Year column is already set to null as shown in below image
    It seems Microsoft just added Hierarchy option in Semantic model. I did not see that this morning but I see it now.
    
    When I drill down from year to QTR it works fine as I did drill in to one year , when I drill in to next level using double down arrow , that is when QTR is being grouped.
    
    Thanks
    Suresh
GilbertQ
Super User
2 years ago
Hi venkatasuresh_g

As mentioned hierarchies are not currently supported in DirectLake
- rodrigues_cleit
  New Member
  1 year ago
  Hello,
  
  Not sure if you guys managed to solve the issue with the Hierarchy.
  After spending some time trying I found a solution:
  
  First I created a DimDate table and populated it using a stored procedure in my Fabric Warehouse:
  (This DimDate table is to attend my needs, you may need something much simpler)
  
  DimDate Table:
  -- Drop and recreate table
  DROP TABLE IF EXISTS dbo.DimDate;
  
  CREATE TABLE dbo.DimDate
  (
  DateKey INT NOT NULL,
  Date DATE,
  Day SMALLINT,
  DayName VARCHAR(10),
  DayShortName CHAR(3),
  DayOfWeek SMALLINT,
  DayOfWeekInMonth SMALLINT,
  DayOfYear SMALLINT,
  WeekOfYear SMALLINT,
  WeekOfMonth SMALLINT,
  Month SMALLINT,
  MonthName VARCHAR(10),
  MonthShortName CHAR(3),
  Quarter SMALLINT,
  QuarterName VARCHAR(6),
  Year INT,
  YearMonth INT,
  IsWeekend BIT,
  IsHolidayJp BIT,
  IsHolidayId BIT,
  FiscalDay INT,
  FiscalMonth INT,
  FiscalQuarter INT,
  FiscalYear INT,
  IsCutOff BIT,
  CutOffDate DATE,
  CutOffDay SMALLINT,
  -- Columns for CutOffDate hierarchy
  CutOffYear INT,
  CutOffMonth SMALLINT,
  CutOffMonthName VARCHAR(10),
  CutOffQuarter SMALLINT,
  CutOffQuarterName VARCHAR(6),
  CutOffYearMonth INT
  );
  
  The you create and run the Stored Procedure: (you can reduce the range of dates, otherwise it will take around 25 min to complete running the query). * The holiday attributes you can update separatelly as you wish.
  
  CREATE OR ALTER PROCEDURE GenerateDates
  AS
  BEGIN
  DECLARE @StartDate DATE = '2024-01-01';
  DECLARE @EndDate DATE = '2032-12-31';
  DECLARE @CurrentDate DATE = @StartDate;
  DECLARE @CutOffDate DATE;
  
  WHILE @CurrentDate <= @EndDate
  BEGIN
  -- Calculate CutOffDate
  SET @CutOffDate = CASE DATENAME(WEEKDAY, @CurrentDate)
  WHEN 'Friday' THEN @CurrentDate
  WHEN 'Saturday' THEN DATEADD(DAY, 6, @CurrentDate)
  WHEN 'Sunday' THEN DATEADD(DAY, 5, @CurrentDate)
  WHEN 'Monday' THEN DATEADD(DAY, 4, @CurrentDate)
  WHEN 'Tuesday' THEN DATEADD(DAY, 3, @CurrentDate)
  WHEN 'Wednesday' THEN DATEADD(DAY, 2, @CurrentDate)
  WHEN 'Thursday' THEN DATEADD(DAY, 1, @CurrentDate)
  END;
  
  -- Check if date already exists before inserting
  IF NOT EXISTS (SELECT 1 FROM dbo.DimDate WHERE Date = @CurrentDate)
  BEGIN
  INSERT INTO dbo.DimDate (
  DateKey, Date, Day, DayName, DayShortName, DayOfWeek,
  DayOfWeekInMonth, DayOfYear, WeekOfYear, WeekOfMonth,
  Month, MonthName, MonthShortName, Quarter, QuarterName,
  Year, YearMonth, IsWeekend, IsHolidayJp, IsHolidayId,
  FiscalDay, FiscalMonth, FiscalQuarter, FiscalYear, IsCutOff,
  CutOffDate, CutOffDay, CutOffYear, CutOffMonth, CutOffMonthName,
  CutOffQuarter, CutOffQuarterName, CutOffYearMonth
  )
  VALUES (
  CAST(YEAR(@CurrentDate)*10000 + MONTH(@CurrentDate)*100 + DAY(@CurrentDate) AS INT),
  @CurrentDate,
  DAY(@CurrentDate),
  DATENAME(WEEKDAY, @CurrentDate),
  LEFT(DATENAME(WEEKDAY, @CurrentDate), 3),
  CASE DATENAME(WEEKDAY, @CurrentDate)
  WHEN 'Saturday' THEN 1
  WHEN 'Sunday' THEN 2
  WHEN 'Monday' THEN 3
  WHEN 'Tuesday' THEN 4
  WHEN 'Wednesday' THEN 5
  WHEN 'Thursday' THEN 6
  WHEN 'Friday' THEN 7
  END,
  DATEPART(DAY, @CurrentDate),
  DATEPART(DAYOFYEAR, @CurrentDate),
  DATEPART(WEEK, @CurrentDate),
  DATEPART(WEEK, @CurrentDate) - DATEPART(WEEK, DATEADD(DAY, 1-DAY(@CurrentDate), @CurrentDate)) + 1,
  MONTH(@CurrentDate),
  DATENAME(MONTH, @CurrentDate),
  LEFT(DATENAME(MONTH, @CurrentDate), 3),
  DATEPART(QUARTER, @CurrentDate),
  'Q' + CAST(DATEPART(QUARTER, @CurrentDate) AS VARCHAR(1)),
  YEAR(@CurrentDate),
  YEAR(@CurrentDate) * 100 + MONTH(@CurrentDate),
  IIF(DATENAME(WEEKDAY, @CurrentDate) IN ('Saturday', 'Sunday'), 1, 0),
  0,
  0,
  DATEPART(DAY, @CurrentDate),
  CASE WHEN MONTH(@CurrentDate) >= 7 THEN MONTH(@CurrentDate) - 6 ELSE MONTH(@CurrentDate) + 6 END,
  CASE WHEN MONTH(@CurrentDate) >= 7 THEN CEILING((MONTH(@CurrentDate) - 6) / 3.0) ELSE CEILING((MONTH(@CurrentDate) + 6) / 3.0) END,
  CASE WHEN MONTH(@CurrentDate) >= 7 THEN YEAR(@CurrentDate) + 1 ELSE YEAR(@CurrentDate) END,
  IIF(DATENAME(WEEKDAY, @CurrentDate) = 'Friday', 1, 0),
  @CutOffDate,
  DAY(@CutOffDate),
  -- CutOffDate hierarchy columns
  YEAR(@CutOffDate),
  MONTH(@CutOffDate),
  DATENAME(MONTH, @CutOffDate),
  DATEPART(QUARTER, @CutOffDate),
  'Q' + CAST(DATEPART(QUARTER, @CutOffDate) AS VARCHAR(1)),
  YEAR(@CutOffDate) * 100 + MONTH(@CutOffDate)
  );
  END;
  
  SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
  END;
  END;
  
  -- Execute the procedure
  EXEC GenerateDates;
  
  Once the above is done, you need to do some setups in the semantic Model view:
  
  1 - Refresh your Semantic Model
  
  2 - Right click on your DimDate Table and select "Make as Date Table"
  
  3 - Toggle the button "On" and select "Date", Fabric has a bug that allows you to selec attributes with repeated values, but if you choose it the date Table won't work correctly, you need to choose an attribubute with unique values, so choose "Date".
  4 - Create a Hyerarchy for CutOffDate:
  
  5 - Set the correct attributes to your hierarchy
  
  6 - In order to solve the sorting problem for Alphabetic attributes such month and quarters, you need to do the following:
  
  For each alphabetic attribute (CutOffMonthName and CutOffQuarterName) you need to choose in Sort by column the corespondent numeric attribute CutOffMonthName = CutOffMonth.
  
  Once the above is done, you are good to go to your powerbi report:
  
  First, refresh you report;
  Second, drag and drop the CutOffDate Hierarchy to you X-Axis of you chart (In case of Vertical Bar Chart):
  As you will see below, the hierarchy is built automatically.
  Then, you can drill your visual char as you want:
  
  HOPE THE ABOVE COULD HELP SOME OF YOU THAT COULDN'T FIGURE OUT THIS DIRECTLAKE LIMITATION.
joakimfenno
Helper V
1 year ago
I have the same issue
even thought I mark the table as date table it does not behave as one
Is the solution to avoid direkt lake storage? what is the suggested solution if you want to have another storage mode for specific table?
van you even mix storage modes (I read that direkt lake is a all or nothing for a semantic model)