Generating Missing Data

[JasonBurdetts]

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.

Hour	Value
2	True
3	True
4	False
6	True
7	True
8	False
11	True

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.

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

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

[Kedar_Pande]

@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

[Rupa01 · Accepted solution]

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]

@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