Retrieve blanks in new table when using the summarizecolumns function

[Chris2016]

Hello,

I am using a query such as the following to retrieve data from Analysis Services:

Query = SUMMARIZECOLUMNS(
    'Table'[CarID],
    'Table'[Status],
    KEEPFILTERS( TREATAS( {123,124,125,128,129,130}, 'Table'[CarID] )),
    "ManufacturedCars", [ManufacturedCars],
    "Released", [ReleasedCars],
    "Designed", [DesignedCars]
)

The database looks somehting like this, with measures for Manufactured, Released, Designed. E.g.: 

DesignedCars = SUM('Table'[Designed]) 

CarIDStatusManufacturedReleasedDesigned

123	Go	1	1	1
124	No go	1	1	1
125	Go	1	1	1
126	Go	1		1
127	Go	1		1
128	Go			1
129	Go
130	No go

The Query results are these:

CarIDStatusManufacturedCarsReleasedDesigned

123	Go	1	1	1
125	Go	1	1	1
124	No go	1	1	1
128	Go			1

 
As you can see, even though I specified CarIDs 129,130 in the query, I am not getting them because the measures for these are blank. How can I modify my query so I get the values for 129,130 even though they have blank measures?

Sample pbix can be dowloaded here: https://www.transfernow.net/dl/202303032OpZwtOO
Thanks!

[OwenAuger · Accepted solution]

Hi @Chris2016 

Try either of these:

1. Wrap every "Expression" argument of SUMMARIZECOLUMNS in IGNORE:

SUMMARIZECOLUMNS(
    'Table'[CarID],
    'Table'[Status],
    KEEPFILTERS( TREATAS( {123,124,125,128,129,130}, 'Table'[CarID] )),
    "ManufacturedCars", IGNORE ( [ManufacturedCars] ),
    "Released", IGNORE ( [ReleasedCars] ),
    "Designed", IGNORE( [DesignedCars] )
)

2.  Wrap SUMMARIZECOLUMNS with ADDMISSINGITEMS as follows:

ADDMISSINGITEMS (
    'Table'[CarID],
    'Table'[Status],
    SUMMARIZECOLUMNS(
        'Table'[CarID],
        'Table'[Status],
        KEEPFILTERS( TREATAS( {123,124,125,128,129,130}, 'Table'[CarID] )),
        "ManufacturedCars", [ManufacturedCars],
        "Released", [ReleasedCars],
        "Designed", [DesignedCars]
    ),
    'Table'[CarID],
    'Table'[Status]
)

I couldn't open your PBIX to verify due to "sensitivity labels" error, but hopefully these work.

[Chris2016]

Hi, Owen,

Thanks so much! The first solution works!

The second is retreiving all ids in the table, and that is not desired:

Thanks a lot for your help!

[OwenAuger]

You're welcome 🙂

Actually the reason the 2nd expression using ADDMISSINGITEMS didn't work as expected is that, in order to apply any filters to the set of rows returned, it needs to be provided a FilterTable argument.

However, as the syntax gets quite verbose, it's probably easiest to stick with the 1st expression.

Regards