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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Coriel-11
Resolver I
Resolver I

Can'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...
Twitter2022-037989
Twitter2022-0310153
Twitter2022-0456712
Twitter2022-047098
Twitter2022-055917
Twitter2022-054846
Facebook2022-034412
Facebook2022-035115
Facebook2022-045598
Facebook2022-044733

 

So to see this

Channel...SourceDate...Impressions...Engagements...

Twitter

2022-03181312
Twitter2022-04127620
Twitter2022-05107513
Facebook2022-039527
Facebook2022-04103211
Facebook2022-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

1 ACCEPTED SOLUTION
johnt75
Super User
Super 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] ) )
)

View solution in original post

2 REPLIES 2
Coriel-11
Resolver I
Resolver I

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

johnt75
Super User
Super 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] ) )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.