Issue with SUMMARIZECOLUMNS

[amikm]

I am trying to create a calculated table using UMMARIZECOLUMNS  DAX that can give me the ShopID, ShopName, R6MonthCount, PrevR6MonthCount

DAX code:

calculated_table =
SUMMARIZECOLUMNS (
Sales[shopno],
Sales[shopname],
"R6MonthCount", CALCULATE ( DISTINCTCOUNT ( Sales[Product] ), Sales[Product] > 0 ),
"PrevR6MonthCount", CALCULATE ( DISTINCTCOUNT ( Sales[Product] ), Sales[PMR6Sales] > 0 ),
"R12Sales", SUM ( Sales[R12Sales] )
)

Here, R6Sales means Sum of Sales till last 6 months from current month

PMR6Sales means Sum of Sales till last 6 months from current month-1

R12Sales means Sum of Sales till last 12 months  from current month

My DAX is working fine If Col3, col4, col5 are grater than 0 but I am getting issues when R6Sales, PMR6Sales and R12Sales are $0 then in that case, it is eliminating the shopno from the visual. I want to display those sites as well even if it is Sales $0 or R6MonthCount=0 or PrevR6MonthCount=0

Expected output (8 records)

Current output: ( 6 records)

@ records got elimited as Column C, Column D and Column E are 0 for  Shopno 1 and Shopno 5,( Issue might be the above DAX)

For Shopno 1 and Shopno 5, I have checked in the DB, R6Sales, PMR6Sales, and R12Sales are $0 

How to fix the DAX?

[amikm]

@TomMartens 

ADDMISSINGITEM  is not working as expected as the column is an aggregate column that we are creating inside SUMMARIZECOLUMNS.I am getting errors while doing it, I thought If we can handle using IF or ISBLANK statement but the problem is it is sliminiating completely If it encouters $0 for those aggregated field

[TomMartens]

Hey @amikm ,

please take the time and create a pbix file containing sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix file to onedrove or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Regards,

Tom

[amikm]

Thanks for efforts, I am able to see the data now , I have used IGNORE() to check If I am getting records or not and It worked

[TomMartens]

Hey @amikm ,

here you will find a description of how to use ADDMISSINGITEM in combination with SUMMARIZECOLUMNS: SUMMARIZECOLUMNS – DAX Guide

Hopefully, this provides what you are looking for.

Regards,

Tom