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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.