Forum Discussion

venkatasuresh_g's avatar
venkatasuresh_g
Icon for Helper IV rankHelper IV
2 years ago
Solved

Hierarchies in Tables when using Direct Lake mode

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 ...
Need Help
  • GilbertQ's avatar
    GilbertQ
    2 years ago

    Hi venkatasuresh_g 

     

    As mentioned hierarchies are not currently supported in DirectLake

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
2 years ago

Hi venkatasuresh_g 

 

As mentioned hierarchies are not currently supported in DirectLake

rodrigues_cleit's avatar
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.