Forum Discussion
Resolver IICan't figure out ADDMISSINGITEMS
Hi Everyone,
I'm trying to wrap my head round using ADDMISSINGITEMS.
I'm trying to add a New Table to the model summarising one of the main tables in my data model. I've tried using both SUMMARIZE & SUMMARIZE COLUMNS, but no matter what I can't get the blank rows to show .
Is it even possible to use with add New Table?
I'm actually trying to do it for when multiple columns (well, 2) are blank. Is this possible? I've followed the SQBLI video here & just can't replicate it with my data.
Channel.. | SourceDate... | Impressions... | Engagements... |
| 2022-03 | 798 | 9 | |
| 2022-03 | 1015 | 3 | |
| 2022-04 | 567 | 12 | |
| 2022-04 | 709 | 8 | |
| 2022-05 | 591 | 7 | |
| 2022-05 | 484 | 6 | |
| 2022-03 | 441 | 2 | |
| 2022-03 | 511 | 5 | |
| 2022-04 | 559 | 8 | |
| 2022-04 | 473 | 3 |
So to see this
| Channel... | SourceDate... | Impressions... | Engagements... |
| 2022-03 | 1813 | 12 | |
| 2022-04 | 1276 | 20 | |
| 2022-05 | 1075 | 13 | |
| 2022-03 | 952 | 7 | |
| 2022-04 | 1032 | 11 | |
| 2022-05 |
But the blank lines are missed out. The best version of the code I've used is:
Test = ADDMISSINGITEMS(
'Table'[Channel],
'Table'[SourceDate],
SUMMARIZECOLUMNS(
'Table'[Channel],'Table'[SourceDate],
"engagements", SUM('Table'[Engagements]),
"impressions", SUM('Table'[Impressions])),
'Table'[Channel],'Table'[SourceDate]
)
I've also tried creating a version of the table without the blank lines and then trying to wrap the ADDMISSINGITEMS code around that.
Neither gives me the blank lines.
Is it just something that can only be done as a virtual table? What am I missing?
Does anyone know of a better explanation?
Matt
As I understand it, ADDMISSINGITEMS will only add in rows where the measure results in a BLANK, but the underlying data row needs to exist in the first place. If you have channels with no rows for a given date then it will not add that in.
You could try
Summary Table = ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Table'[Channel] ), VALUES ( 'Table'[Date] ) ), "Impressions", CALCULATE ( SUM ( 'Table'[Impressions] ) ), "Engagements", CALCULATE ( SUM ( 'Table'[Engagements] ) ) )
2 Replies
johnt75Super User
As I understand it, ADDMISSINGITEMS will only add in rows where the measure results in a BLANK, but the underlying data row needs to exist in the first place. If you have channels with no rows for a given date then it will not add that in.
You could try
Summary Table = ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Table'[Channel] ), VALUES ( 'Table'[Date] ) ), "Impressions", CALCULATE ( SUM ( 'Table'[Impressions] ) ), "Engagements", CALCULATE ( SUM ( 'Table'[Engagements] ) ) )
