Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hoongary
Frequent Visitor

Make SUMMARIZE / ADDCOLUMNS to retain blank rows

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?

1 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.


View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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.


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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.