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