Any better ways to hide blank columns in a matrix?

[Anonymous]

Hi all,

I have a very nice matrix that i can filter by both "market type" and "position". However there are alot of values which causes blank columns. I have read everyone's suggestions to remove word wrap and manually hide these columns. However in my case, the gaping between the data looks sily:

Any ideas?

Thanks,

Brian

[Anonymous]

I figured it out! It isnt difficult to do, just takes a few steps! To be able to filter a matrix however you want and have no annoying spacing in between values, the key is UNION. Check it out!

Unfiltered:

Filtered:

Works as expected! How you can make this work:

Step 1: Create individual tables with the filter variables that you want to be able apply to the common value ('Pricing' in this case). You can have as many filters applied to the common value as you want! Example:

 Build as many of these tables as you need!

Step 2: Create your filter tables. These should match your column headers of the individual tables. The filter tables i created was: 

1. Calendar

2. Position

3. Market Type

4. Product

Step 3: The majic is taking all those individual tables and creating a new aggregated table using UNION. After theUNION table is created, link that puppy up with your filter tables and you will see this:

The last step is to fill out the columns/ value for the matrix table in the visual and then drill down on columns using the "expand all down" button.

Enjoy!

[Greg_Deckler]

@Anonymous See if this works for you: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

[Anonymous]

And to add insult to injury, if i use a table and format my columns differently then i am able to remove the gaps but then get blank columns when filtering:

Seems like i cant win either way.

[v-easonf-msft]

Hi ,  @Anonymous 

It looks like you dragged a lot of duplicate fields into the matrix Values .

You may take a try  the option "show item with no data"

Best Regards,
Community Support Team _ Eason

[Anonymous]

I changed the "show items with no data" for both 'unchecked' and 'checked'. Nothing changed. It would seem to make sense to me that if "show items with no data" was 'unchecked' that the blank columns ought to disappear?