SUMMARISECOLUMNS ISSUE

Ok!

Move the FactMain filter into your base measure instead, and keep SUMMARIZECOLUMNS clean:

Step 1 : Redefine your base measure (or create a new one):

Total Rev Revenue = 

CALCULATE(

    [Total Rev],

    FactMain[ColumnType] = "Revenue"

)

Step 2 : Rebuild the table without the FILTER argument:

s1 = 

SUMMARIZECOLUMNS(

    DimCalendar[Year],

    DimCalendar[Month],

    "Revenue", [Total Rev Revenue],

    "Revenue Per Year",

        CALCULATE(

            [Total Rev Revenue],

            ALL(DimCalendar[Month])

        )

)

REMEMBER ME: Never use FILTER(FactTable,...) directly inside SUMMARIZECOLUMNS when you also need context manipulation in the same table. Always push those filters into your measures, this is the golden rule for avoiding this exact class of bugs.

Great news that it's working!

The Blank Row Explained

That blank row with 0.00 is a classic Power BI behaviour — it represents a "blank" member that SUMMARIZECOLUMNS automatically generates when there are rows in your FactMain table that do not match any row in DimCalendar.

In other words, some fact rows have a date key that either:

Is NULL / blank

Points to a date that doesn't exist in your DimCalendar table

So Power BI groups all those "unmatched" fact rows under a blank Year and blank Month bucket.

To fix it, simply wrap your SUMMARIZECOLUMNS with FILTER to exclude blank years:

s1 =
FILTER(
SUMMARIZECOLUMNS(
DimCalendar[Year],
DimCalendar[Month],
"Revenue", [Total Rev],
"Revenue Per Year", CALCULATE([Total Rev], ALLEXCEPT(DimCalendar, DimCalendar[Year]))
),
NOT ISBLANK(DimCalendar[Year])
)

Hi oussamahaimoud ,

Thank you for explaining the blank rows and you are right, my fact table does have rows where the date is blank. That explains it!

Thank You! 🙂

Hi mp390988,

Thank you for reaching out to Microsoft Fabric Community.

Thank you oussamahaimoud for the prompt response.

Yes, using the FILTER(FactMain, ...) directly inside SUMMARIZECOLUMNS is generally not considered as best practice, especially on large fact tables.

The best approach is to move the filter logic into measures using CALCULATE and then use the measures inside SUMMARIZECOLUMNS:

For example:

Rev Filtered =
CALCULATE(
    SUM(FactMain[GBPRevenue]),
    FactMain[ColumnType] = "Revenue",
    NOT ISBLANK(FactMain[TDMonth])
)

Vol Filtered =
CALCULATE(
    SUM(FactMain[GBPVolume]),
    FactMain[ColumnType] = "Revenue",
    NOT ISBLANK(FactMain[TDMonth])
)

Now use the measures inside SUMMARIZECOLUMNS:

AllClients =
SUMMARIZECOLUMNS(
    FactMain[TDMonth],
    FactMain[TD Month Formatted],
    FactMain[TradeDate],
    FactMain[DealerGroup],
    FactMain[DealerID],
    FactMain[ClientID],
    FactMain[ClientName],
    FactMain[Product],
    "Rev", [Rev Filtered],
    "Vol", [Vol Filtered]
)

This approach is usually preferred because it improves the readability, reusability and overall query optimization.

Thanks and regards,

Anjan Kumar Chippa