Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Matrix data: Show empty columns when filtering (like a date hierarchy)

Hi powerBI community, 

 

I would like to build a matrix visual with "week number" as a column and some counter as a value. 

The problem is I'm not able to find a way to do so in a way that will keep blank columns, when filtering is applied. 

 

I tried without filtering: 

 

It works. But, when I add any filter, blank columns are not there anymore: 

As you can see, the week numbers are jumping from 2 to 5 to 7, etc.. instead of 1, 2, 3, 4, ... 

 I tried the following: 

  • check the "show items with no data"
  • use another table, which has all the possible week numbers (but is still related in the model to my data, to allow filtering) 

But, none of the following worked for me. 

I know it is possible because with date hierarchies, I'm able to reproduce the expected behavior: 

 

But, since I want to use "week number", which is not part of the classical date hierarchy, I have to create a custom column for it. 

So, can anyone help me? 

Thanks a lot for the great support that you give on this forum 

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I've found that when the "show items with no data" box doesn't work, you need to pair it with a reference table that you manually create in the data model. Create a new table that has a single column that contains all possible week numbers in it (your Week Number Reference table). Then connect the Week Number Reference table back to your original data on the Model tab (making sure that you select "Single" as the cross-filter direction when setting up the relationship between Week Number and Week Number Reference columns). 

     

    Now replace the week number in your matrix with week number reference from your reference table. You still want to make sure that "Show items with no data" is selected for week number reference. Now all of the week numbers should remain visible even as you use your filters. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      THANK YOU! 
      Had the same issue and this solved it for me

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you! It's the only solution that works for me!

  • mcolqs's avatar
    mcolqs
    New Member
    8 months ago

    An easy way to fix this:

    1. Add a new row which will always have data
    2. In visual calculations editing mode, hide this row
  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

    Based on my test, the "show items with no data" could work on my side:

    I could not understand the sentence "when filtering is applied", could you mean the slicer in above picture?

    If I misunderstand you, could you please share your sample file and offer me more information to have a test?

     

    Regards,

    Daniel He

     

     

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

    Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

     

    Regards,

    Daniel He