Matrix shows all columns even if empty

[aeezm]

Dear community,

I took over this dashboard from a colleague and there is this matrix visual that is supposed to show the costs/income per cost center.

As you can see in the screenshot, there are a lot of empty columns because these cost centers don't have any data for the month selected. If you would select a month in 2023 or 2022 they may have data but somehow the matrix table always shows all cost centers that are available in the cost center dimension table and not only the once that have data for the selected month.

This is the measure used:

Current values =

Var x=SELECTEDVALUE('0 Template'[Details])

Return

switch(true,      

    x="Revenues",FORMAT([Revenue],"#,##0;(#,##0)"),

    x="External Expenses",FORMAT([External Expenses],"#,##0;(#,##0)"),

    x="Other Expenses",FORMAT([Other Expenses],"#,##0;(#,##0)"),

    x="Other Income",FORMAT([Other Income],"#,##0;(#,##0)"),

    x="Check",[Check],

    x="CM I",FORMAT([CM I],"#,##0;(#,##0)"),

    x="CM I  in %",Format([CM I in %],"percent"),

    x="Costs A",FORMAT([LC RC],"#,##0;(#,##0)"),

    x="Costs B",FORMAT([TP-F ytd],"#,##0;(#,##0)"),

    x="Costs C",FORMAT([TP-P ytd],"#,##0;(#,##0)"),

    x="Costs",FORMAT([Costs I],"#,##0;(#,##0)"),

    x="CM II",FORMAT([CM II],"#,##0;(#,##0)"),

    x="CM II  in %",FORMAT([CM II in %],"percent"),

        FORMAT(CALCULATE([Final Data Value],'0 Account Deatails'[Category]=X),"#,##0;(#,##0)"))

Since the list of cost centers is very long it is hard to work with this visual because you can not even scroll to the right side to see the total sum column, you need to export the data to be able to see it.

If you would use the measures seperately (instead of the switch measure), you only see the cost centers that have data for the selected month and no more empty columns:

But since you are supposed to see data for all categories this does not work.

I have already tried to check the relationships, activate the "is not blank" in the filters pane and to modify the measure but it did not work so far.

Do any of you have any ideas how to solve this problem?

Thank you in advance!

[aeezm · Accepted solution]

I found out what the problem was, it was because of the FORMAT function that the blank columns appeared.

[Joe_Barry]

Hi @aeezm 

It could be that there is a single value in one of each of the Cost Centers, this will show blanks if there is no data. Example all CC except one will have a value, but Blanks will appear for the others, just because of that one.

If you add the measures separately and the correct values are being shown, if you want to see the invisible categories, then in the Build section on the right side, right click on Categories and choose Show items with no Data. But You will get the same result as the first screenshot.

Joe

[aeezm]

Hi @Joe_Barry thank you very much for your reply! 
You are right, when I activated "Show items with no data" the empty columns appear.

However I unfortunately didn't understand the first part of your message completely, you mean that some of the cost centers don't have any value for one of these categories?