Matrix Filtering Column Items by Another Table

Hi Everyone

I have a matrix that I am struggling to filter because I want to show blanks as zeroes in the matrix.

I have two levels of master data and one fact table. If I filter by the lowest level of master data, the column items filter correctly but if I filter by the higher level of master data, instead of filtering out column items, the values show as zero.

I would appreciate it if someon could assist me - is there a way to show blanks as zeroes but filter column items by a higher level of master data?

Data Structure

3 Master Data tables (A, B and C) and 1 Fact Table

Table A links to Table B by means of a foreign key. The relationship is bi-directional.

Table B links to the Fact Table by means of a foreign key. Table B filters the Fact table

Table C links to the Fact Table by means of a foreign key. Table C filters the Fact table

Matrix Structure

Table C items added as rows

Table B items added as columns

Calculated measure from the Fact Table as value. Blanks shown as 0

Current Behaviour

If I filter by Table B, only the filtered items show in the matrix

If I filter by Table A, all Table B items show but the items not in the filter criteria show all values as 0

Note: this issue only occurs if the Table B items are in the columns. If Table B items are added as rows, instead of columns, the matrix behaves as desired.

Desired Behaviour

If I filter by Table A, I want only the relevant items in Table B to show in the Matrix, like would be the case if I filtered Table B items or as would be the case if Table B items were added as rows instead of columns.

Sorry for the vagueness - I would be happy to clarify any point.