Unexpected behaviour - Matrix visual...

[Matty]

Hi,

I have a fact table (many) and a dimension table (one) with a relationship set up between the two.

I have created a matrix that is bringing values (product descriptions) in from the dimension table (as 'Rows'). I have then created a measure using data held exclusively in the fact table, which I have also brought into the matrix (as 'Values').

The issue I have is that when I bring the measure into the matrix, records show for all of the product descriptions in the dimension table even though there is no data for some in the fact table.

Is there something I need to include in my measure to suppress the product descriptions not existing in the fact table?

Hope the above makes sense.

Cheers,

Matty

 

[Matty]

Hi,

If it helps, the following shows what's happening:

[TABLE="width: 128"]
<tbody>[TR]
[TD="colspan: 2"]Fact Table[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 132"]
<tbody>[TR]
[TD="colspan: 2"]Dimension Table[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Packaging[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Bag[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Loose[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Box[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Bag[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 196"]
<tbody>[TR]
[TD]Matrix[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Packaging[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Bag[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Loose[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Box[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Bag[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, Orange is appearing in the Matrix, even though it does not exist in the Fact table. The only reason I can guess for this behaviour is due to the relationship existing between the Fact and Dimension tables, causing all Products from the Dimension table to be be returned, regardless of whether the Product exists in the Fact table (which my measure is calculated from).

Can someone demystify what's going on here?

Cheers,

Matty

 

[gazpage]

https://powerpivotpro.com/2017/08/autoexist-cross-table-filtering/

 

[gazpage]

Oh, and just add a filter for measure not equal to 0

 

[Matty]

Oh, and just add a filter for measure not equal to 0

Thanks for the link. It explains things well.

Cheers,

Matty