cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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: 1541586971-no-filter

 

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

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: 

1541587131-date

 

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 

 

4 REPLIES 4
v-danhe-msft Super Contributor
Super Contributor

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

Hi @Anonymous,

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

1.PNG

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

 

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-danhe-msft Super Contributor
Super Contributor

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

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kris123 Visitor
Visitor

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

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. 

Highlighted
HugoDEVILLIER Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 472 members 3,587 guests
Please welcome our newest community members: