Medical Student: But wasn't that the whole basis of your grandfather's work, sir, the reanimation of dead tissue?Freddy: My grandfather was a very sick man.by The FrankensTeam While building up my first PowerPiovot database, I realized a strange problem: tons of blank rows appeared in the final pivot table. Here I would like to share with you the details and how we can avoid it. Hungarian version of this post is available here.## Database## The problemIn the particular case it means thousands of rows mixed with rows with values. Obviously I did not want to give this "sick" report with "dead" codes to the users. ## InvestigationI realized the report contains data for these "dead" codes only in calculated columns,where I used a constant value in the DAX formula. (In the above picutre TMI% column is a calculated one.)I put together a small example to make it clear. And the sales data table:I loaded both tables to PowerPivot and joined with the Product ID field. You can see products number 7,8,9,10 are not represented in the data table. These products (and only these) belong to Product group x and y. I created the first calculated field for turnover growth (I used summarized measures): `Growth % 1:= DIVIDE( [turnover 2015] , [Turnover 2014] , 0 )` Looks to be correct. But in our company the definition of Growth is different: we show only the value above 100%, so I have to subtract one: Using this new growth rate the pivot shows the groups without data too:`Growth % 2:= DIVIDE( [turnover 2015] , [turnover 2014] , 1 ) - 1` Clearly the problem is the -1 constant value at the end of the formula. I tried with IFERROR too: `Growth % 3:= IFERROR( [turnover 2015] / [turnover 2014] -1 , 0 )` The result is the same: pivot contains "dead" products. The only way I managed to eliminate the unnecessary rows is to change the calculation formula this way, using no constant value: `Growth % 4:= IFERROR( ([turnover 2015]-[turnover 2014]) / [turnover 2014] , 0 )` ## What happened?Based on the join between the Product IDs, Excel builds up the calculation background table, containing ALL the Product IDs, but BLANK rows (without data) will not appear in the final pivot table.The problem is generated by the calculated field: in PowerPivot BLANK + constant = constant, so the calculation gives a result for the BLANK rows (these are non-BLANKs anymore) and they will appear in the pivot table.DottorGábor: if we think with SQL codes, it seems as if the JOIN type had been changed: now it is OUTER JOIN instead of INNER JOIN. It's creepy because our pivot shows data which does not exists - only as a result of the way we built up the calcuation formula!## SolutionWell... I do not know what is the reason behind this strange way of using BLANK, it only makes problem for me. Fortunately the measures we use can be changed in a way without constant - this is the only solution I found for the problem.if you would like to take a look at the example file, you can download from here. |