I'm working with PowerPivot and have a column that, at some points in time, is completely NULL for every row. When this happens the column disappears completely from my PowerPivot table. Is there a way to always have this column in the table even though it's completely null?
There may be several ways to handle this depending on exactly what your report looks like and what data you want to show in your other columns.
The first and easiest thing to try is to right-click anywhere in your pivot table and go down to PivotTable Options near the bottom.
In PivotTable options, fo to the Display tab and about 1/2 way down you should see checkboxes for "Show items with no data on rows and columns."
Check the one for columns. This will show that column in your pivot even if it is empty.
Wow. I have NO IDEA why altering the underlying MDX statement with "Show Empty" changes the behavior of the PowerPivot table, but it does. Any idea why?
I'm not sure if this will work for my user or not since their example has numerous cross joins and allowing empty is probably going to explode the results to a ridiculous number but, regardless, this is the first thing I've seen that lets me see that third column.
There are people on here who know far more about MDX than me and can probably explain it much better, but I believe it simply boils down to performance. Ignoring empty tuples helps speed up retrieval from the cube.
The PowerPivot table is simply using MDX to populate itself. If the query doesn't return anything then there is nothing to populate. Sorry, that's the best I can do.
Another suggestion might be to write a calculated member that returns something in place of null so that it is never seen as empty. Then just account for that placeholder value in your DAX measure in powerpivot after the data is imported. That might work if you see your data set grow exponentially from joins.
Again, there are others on here who know far more about MDX than me so they might have a better suggestion.
Actually that answers my question. I didn't realize that the MDX designer would show a completely NULL column when the actual results from the MDX look like this:
So, completely my misunderstanding of the designer - I thought if it showed a column that the column also showed up in the result set. Now PowerPivot's behavior makes total sense...though I take issue with how the MDX designer interprets results now. :)
Your placeholder value via MDX makes sense as well. DAX can't handle the nonexistence of a column, at least I couldn't figure out how it would, but it would be able to handle a "default" MDX value and I hadn't considered that.