Make SUMMARIZE / ADDCOLUMNS to retain blank rows

[hoongary]

Hi Everyone,

I'm looking for help with a SUMMARIZE / ADDCOLUMNS related problem.
I have the below DAX query:

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
            SUMMARIZE (
                'FACT Sales',
                'DIM Product '[product_name],
                'DIM Territory'[territory_name]
            ),
           "CY_Sales", [Total Sales],
           "PY_Sales", [PY Sales]
    ),
    'DIM Date Table'[Year] = 2024
)

My goal would be to include rows related to both CY_Sales and PY_Sales in the results when at least one of these columns is not blank.
With the above solution, CY_Sales doesn't include blank values, only PY_Sales has blank values.
(PY Sales is using SAMEPERIODLASTYEAR())

What should I do to retain the rows where CY_Sales is blank, but PY_Sales is not blank?

[Greg_Deckler]

@hoongary Not having any success simulating this. See attached PBIX below signature. I used 2014 instead of 2024 for the year filter. Can you provide sample data that emulates this issue?

[hoongary · Accepted solution]

@Greg_Deckler 
Looks like I've found a working solution:

EVALUATE

VAR _CrossJoin =

CROSSJOIN(

    VALUES('DIM Product'[product_name]),

    VALUES('DIM Territory'[territory_name])

)

VAR _BaseTable =

CALCULATETABLE(

    ADDCOLUMNS(

       _CrossJoin,

           "CY_Sales", [Total Sales],
           "PY_Sales", [PY Sales]

       ),
    'DIM Date Table'[Year] = 2024

)

VAR _FilteredTable = FILTER(_BaseTable, NOT ISBLANK([CY_Sales]) || NOT ISBLANK([PY_Sales]) )
RETURN

_FilteredTable

With using crossjoin first on the required columns, I'm able to get the rows where CY or PY Sales is blank.
I'm using the _FilteredTable to get rid of unrequired rows where both CY and PY Sales are blank.

I was worried of the performance, because the real data has more than ~10 million rows, but the performance seems to be pretty good.

[hoongary]

Hi @Greg_Deckler ,

Sure, recreated the issue in a tiny sample report: 
link

Added a slicer to the report page which is filtered for Year = 2024, so Total Sales will show CY Sales in the chart.
For 2024, there is no sales for the Product B and Territory B combination, so Total Sales (CY) is blank.
However, there is PY sales for the Product B and Territory B combination, so PY Sales is not blank.

Things are the reverse for the Product D and Territory D combination, Total Sales (CY) is not blank, PY Sales is blank.

Please check the DAX Query view, here using the SUMMARIZE / ADDCOLUMNS logic, the query results won't include the Product B and Territory B combination, where CY is blank, PY is not blank, but it includes the Product D and Territory D combination, where CY is not blank, PY is blank.

I need the row in the query result where CY is blank, but PY is not blank.