Forum Discussion

JasonBurdetts's avatar
JasonBurdetts
Frequent Visitor
1 month ago
Solved

Generating Missing Data

I am attempt to solve an issue where I believe I am starting with some misconceptions, so I would like to explain a very simple scenario, the solution and why the solution works.

Image a Dimension table having Hours 1 to 12 (in practice, a Date table, but I am using hours here to demonstrate).

Hour
1
...
12


Imagine a Fact table having series data Each Value entry is either true, false, or we don't have a data point for the specific hour as in the below table.

HourValue
2True
3True
4False
6True
7True
8False
11True

 

How do I use DAX (or Power Query if more appropriate) to essentially create a SQL left join so that every Hour is represented in the second table, and that blank Values are filled with the value that is the first previous (in hour order) non empty value? In the below table, new values represented in italics.

 

HourValue
1False
2True
3True
4False
5False
6True
7True
8False
9False
10False
11True
12True


This a very simple representation of what I am trying to understand after struggling with LASTNONBLANK and similar functions. The actual Value column is an aggregate of integer entries that that occur in that hour, in which I'd like to use the first previous aggregated value.

 

Jason

  • Rupa01's avatar
    Rupa01
    1 month ago

    Hi JasonBurdetts,

    Yes, this can be achieved either with DAX or Power Query, but based on your explanation, Power Query may be the simpler option. Since your actual Value column is an aggregate of integer entries that occur within each hour, the key is to aggregate first, then fill down.

    The correct order would be:

    1. Aggregate the fact table by Hour
    2. Left join/merge it with the full Hour/Date dimension table
    3. Sort by Hour ascending
    4. Expand to get Value Column
    5. Fill down the aggregated value column. Note: First row would be null after applying Fill down, replace null with 'FALSE'.

     

    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer

3 Replies

  • Rupa01's avatar
    Rupa01
    Solution Sage
    1 month ago

    Hi JasonBurdetts,

    Yes, this can be achieved either with DAX or Power Query, but based on your explanation, Power Query may be the simpler option. Since your actual Value column is an aggregate of integer entries that occur within each hour, the key is to aggregate first, then fill down.

    The correct order would be:

    1. Aggregate the fact table by Hour
    2. Left join/merge it with the full Hour/Date dimension table
    3. Sort by Hour ascending
    4. Expand to get Value Column
    5. Fill down the aggregated value column. Note: First row would be null after applying Fill down, replace null with 'FALSE'.

     

    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer
    • Rupa01's avatar
      Rupa01
      Solution Sage
      1 month ago

      JasonBurdetts If your require to use DAX then pull the hour from the Dim table to the visual and add below DAX Measure to it to get the desired result.

      Filled Value = 
VAR CurrentHour =
    MAX ( DimHour[Hour] )
VAR PreviousFactHour =
    CALCULATE (
        MAX ( Fact[Hour] ),
        FILTER (
            ALL ( 'Fact' ),
            'Fact'[Hour] <= CurrentHour
                && NOT ISBLANK ( Fact[Value] )
        )
    )
RETURN
    IF (
        ISBLANK ( PreviousFactHour ),
        BLANK (),
        CALCULATE (
            SELECTEDVALUE ( 'Fact'[Value] ),
            FILTER (
                ALL ( 'Fact' ),
                'Fact'[Hour] = PreviousFactHour
            )
        )
    )

      🎯Result -

       

      💡 Helpful? Give a Kudos 👍 — keep the community growing
      ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

      Best regards,
      Rupasree Achari | BI & Fabric Analytics Engineer 
  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 month ago

    JasonBurdetts 

    Do this in Power Query, much cleaner than DAX here.

     

    Start from your Hour list (1-12) and left join the Fact table on Hour. Missing hours show null.
    Sort by Hour ascending.
    Right-click the Value column, Fill > Down. That carries the last value forward into the blanks.
    Replace any leftover null at Hour 1 with False.

     

    If this answer helped, please click 👍 or Accept as Solution.
    -Kedar
    LinkedIn: https://www.linkedin.com/in/kedar-pande