How to Show all Columns for a DataSet When No Data Exists for That Row/Column Intersection


  • I'm building some pivot tables off of some PowerPivot data. I have charts that I want to create off the pivoted data where the X-Axis are months and the Y Axis is some value. I have my DAX filter only return the last 12 months of calendar data because we only want to chart the last 12 calendar months. The issue is when there are no values for some of the months. In those cases the pivot table ends up only showing the months that have data. I need the pivot to show ALL 12 months and then put 0's in the months with no data. I've tried changing the pivot table options to show blank values with 0's, but that does not do it. I want the charts to tie to the pivot table instead of trying to use a bunch of vlookups on the pivoted data to create the 12 month charts.

    How can I show all of the columns even when there is no data for that column?


    Tuesday, January 31, 2012 3:38 PM


  • Hi Whalensdad

    Have you tried detecting for BLANK()?

    Suppose your formula above is called [Values].  You could then use the following

    =IF([Value] = BLANK(), 0, [Value])

    And then use this new calculated column in your chart.  It should display zero for the months with no data

    Javier Guillen
    Wednesday, February 01, 2012 8:56 PM

All replies

  • Are you using a Date table?

    Please show the formula for the measure.

    Tuesday, January 31, 2012 5:33 PM
  • =SUMX(FILTER(RFC_Error_Totals_By_Subsystem_Program_ALL,RELATED(Act_Rel_Date_Lookup[DATEDIFF]) < 12 ),RFC_Error_Totals_By_Subsystem_Program_ALL[SUMTHECOUNT])


    I list each collection month with how many months it is different from the very last collection month. I.e. If Dec 2011 is the last collection, Nov 2011 has a datediff of 1, Oct 2011 has a datediff of 2, etc.

    Tuesday, January 31, 2012 8:00 PM
  • Please try to add 0+ at the begining.

    =0+ SUMX(FILTER(RFC_Error_Totals_By_Subsystem_Program_ALL,RELATED(Act_Rel_Date_Lookup[DATEDIFF]) < 12 ),RFC_Error_Totals_By_Subsystem_Program_ALL[SUMTHECOUNT])

    Tatyana Yakushev []
    Tuesday, January 31, 2012 10:16 PM
  • Did not work.
    Tuesday, January 31, 2012 10:25 PM
  • If you are not currently using a Date table then you might want to try adding a side table with months, relating it to your fact table on month and then use the month column in the related table as your x-axis. The months with no data don't show up because of MDX autoexists. See the link below with a related issue.

    Tuesday, January 31, 2012 10:30 PM
  • I tried that as well. The RELATED(Act_Rel_Date_Lookup[DATEDIFF]) is a table with all of the dates. Even using that as the x-axis made no difference.
    Tuesday, January 31, 2012 10:34 PM
  • Hi Whalensdad

    Have you tried detecting for BLANK()?

    Suppose your formula above is called [Values].  You could then use the following

    =IF([Value] = BLANK(), 0, [Value])

    And then use this new calculated column in your chart.  It should display zero for the months with no data

    Javier Guillen
    Wednesday, February 01, 2012 8:56 PM
  • I don't know why you marked this as the answer. It did not work.

    =if(Error_Totals[Last 12 Months] = Blank(),0,Error_Totals[Last 12 Months]) retrurned only those months with values.


    Tuesday, February 07, 2012 1:41 PM
  • Forget the chart for a second. What does the underlying pivot table show? Does it show zero for those months?

    Tuesday, February 07, 2012 2:00 PM
  • I haven't even created the chart. The pivot table only shows the months with data. As an example:

                      3/1/2011               7/1/2011               9/1/2011

    Value 1           0                            8                           2

    Value 2           1                            3                           0

    Value 3           0                            0                           4

    Tuesday, February 07, 2012 2:06 PM
  • What if you create a measure TestZero:=0. What does this show? Does it show up in your pivot table?
    Tuesday, February 07, 2012 2:28 PM
  • I'm not sure what you are suggesting. Can you please explain?
    Tuesday, February 07, 2012 2:32 PM
  • I am having a hard time understanding why Tatyana's answer did not work for you if you're using a related Date field in your pivot table's columns. My test file shows zero even for months where there is no data. Can you provide a sample file?
    Tuesday, February 07, 2012 2:39 PM
  • Unfortunately, due to corporate policy, I can't send a file.


    Tuesday, February 07, 2012 3:03 PM
  • Construct a sample file with some dummy data in it that illustrates the problem you are having.

    ...or did you mean that you can't send any files?
    • Edited by ruve1k Tuesday, February 07, 2012 3:15 PM
    Tuesday, February 07, 2012 3:14 PM
  • I can't send ANY files. The best I can do is descibe the 2 underlying tables.

    Table 1 is the table with the dates. It has 3 columns. Column 1 is the Last day of the month. There is a row for each of the last 24 months. Column 2 is the difference between the current month and the date in the first column. So as an example:

    Date                Date Diff

    1/31/2012             0

    12/31/2011           1

    11/30/2011           2

    Table 2 is the fact table. It contains the data I want to pivot. There are 3 columns of importance. Column 1 is the Date column. It contains a date that matches a date in the date table. Column 2 is an ID. It contains some identifcation for the data being aggregated. Column 3 is a sumation of how many records are in the original data set that match the ID and Date. So as an example:

    Date                  ID                       SumCount

    1/31/2012     AAA-1                            3

    1/31/2012     AAA-2                            1

    1/31/2012     AAA-3                            4

    1/31/2012     BBB-1                            2

    12/31/2011   AAA-2                            6

    12/31/2011   AAA-3                            1

    11/30/2011   AAA-1                            2

    11/30/2011   BBB-1                            1

    If I wanted to see the last 3 months of AAA my table would look like:

                        11/30/2011              12/31/2011             1/31/2012

    AAA-1                   2                               0                             3

    AAA-2                   0                               6                             1

    AAA-3                   0                               1                             4

    If I wanted to see BBB:

                         11/30/2011                  1/31/2012

    BBB-1                   1                                  2

    This is the best I can do.

    Tuesday, February 07, 2012 3:36 PM
  • OK. I agree, it seems that what you're trying to achieve is a bit tricky.

    The formula that I used in my test file looks like this:


    I am going to try and translate that into the names used in your model.

    Let me know how that works.

    Tuesday, February 07, 2012 4:24 PM
  • Unfortunately the same result. here is the measure:

    =SUMX(FILTER(Totals_By_Subsystem,RELATED(Act_Rel_Date_Lookup[DATEDIFF]) < 12 ),0+Totals_By_Subsystem[SUMTHECOUNT])

    BTW I appreciate the help.
    • Edited by Whalensdad Tuesday, February 07, 2012 4:57 PM
    Tuesday, February 07, 2012 4:56 PM
  • Why did you switch the filter parameters?

    I am intentionally applying the filter to and iterating over the Date table and not the fact table.

    Please try the formula exactly as provided and let me know your result.


    Tuesday, February 07, 2012 5:30 PM
  • I hadn't seen that you changed the measure paramters. So I tried:

    =SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+[SUMTHECOUNT])

    And I get "Cannot find column "SUMTHECOUNT"

    So I tried

    =SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+Totals_By_Subsystem[SUMTHECOUNT])

    and I get "The value for column 'SUMTHECOUNT' in table 'Totals_By_Subsystem' cannot be determined in the current context"

    So I tried

    =SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+Related(Totals_By_Subsystem[SUMTHECOUNT]))

    and I got "The column 'Totals_By_Subsystem[SUMTHECOUNT]' either doesn't exist or doesn't have a relationship to any table availabe in the current context"  Which it most definately does.
    Tuesday, February 07, 2012 6:10 PM
  • Sorry, I thought [SUMTHECOUNT] was a measure.

    Please create a measure SumTheCountMeasure:=SUM(Totals_By_Subsystem[SUMTHECOUNT])

    and then use that measure in place of [SUMTHECOUNT].

    =SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),

    ...or maybe you could just wrap the column reference in a SUM. (not tested)

    =SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),

    • Edited by ruve1k Tuesday, February 07, 2012 6:21 PM
    Tuesday, February 07, 2012 6:19 PM
  • I created a SumTheCountmeasure as you indicated and then tried:

    =SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+[SumTheCountMeasure])

    And there was no difference in the result.

    Tuesday, February 07, 2012 6:29 PM
  • Well, it works for me and I'd be happy to send you my test file.
    Tuesday, February 07, 2012 8:14 PM
  • Also, please confirm (again) that the columns in your pivot table are from the Date field in Act_Rel_Date_Lookup (Date Table) and not fromTotals_By_Subsystem (Fact Table).
    Tuesday, February 07, 2012 8:17 PM
  • Eureka! I was using the date column from the Fact table and not the lookup table. Somehow I missed that. Thanks for the assist. I ended up using

    =SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ), 0+[SumTheCountMeasure])

    along with changing the column and it works.

    Thanks again.

    Tuesday, February 07, 2012 8:35 PM
  • Sweet!!!

    "I love it when a plan comes together! " ~Hannibal (The A-Team)

    • Edited by ruve1k Thursday, February 09, 2012 4:14 AM
    Tuesday, February 07, 2012 8:41 PM
  • those ways are way too difficult... EASIEST WAY:

    Go to your Pivot Table's Field List

    Go to the Column Labels--> click on whatever you're using as the columns ex. I used subinventory

    Select Field Settings

    Go to the Layout & Print Tab

    And there is an option that say "show items with no data"

    and THERE YOU GO! 

    Solves issues without any formulas... :) 

    Thursday, August 16, 2012 2:12 PM