Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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] ) )
)
Thanks @johnt75 - that not only tells me something about ADDMISSINGITEMS that I guess was obvious, but also wasn't(!), but also gives me something that works for what I wanted.
Plus now I have a new function to go and find out about.
Much appreciated,
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] ) )
)
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |