DAX "SUM" handling of blanks

[cr731]

I have Power Pivot tables with calculated fields using the SUM formula.

If one of the columns being summed is blank, the refresh results in an error saying "The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String."

I need to find a way to handle this situation, because I have many data tables and it is very possible that not all of them will have data in them all of the time.

A normal pivot table would just return a SUM of 0 rather than error out. It also does not work to use IFERROR ... because it isn't that the calculated field returns an error, it's that the actual refresh will not complete and an error message pops up.

 

[ImkeF]

The description isn't quite clear to me: Do you have missing values

1) in the date column of your Transaction/data tables or
2) in the amount column?

If its 2), then you should try to filter these lines out during your Import process. (Garbage in garbage out

But the errror messages indicates 1) and then your amount probably shouldn't be ignored in your SUM - so then you'd need to check for errors in your date columns during Import. I'd consider using PowerQuery for this task.

hth, Imke

 

[cr731]

It's basically where I have 10 data tables that have Power Pivots built off of them... and that of these 10 tables, there are circumstances where the entire table will be blank for a particular table. There isn't really anything I can do about it, because each table contains different data and for some of the files, they simply don't have this data. So when the data table is entirely blank, the Power Pivot fails on refresh. Not sure what the best way to address this would be.

 

[ImkeF]

Oh I see - I cannot think of a pure PP-solution here. PP needs to validate the existing relationships (1:1 or 1:n) and if there are no rows the table cannot stay connected in the data model.

But PowerQuery can help you here. I guess that the potentially empty tables are transaction tables: Join them together with a table that will always have lines. Even if they have completely different structure and key fields: You can consolidate them union-like into one table by attaching them. Even if they don't share a single column, you will maintain all fields of both joined tables and thereby make sure that you don't end up importing empty tables into PowerPivot.