Can PowerPivot do this?

[J F V]

Hi all,
I am stuck trying to visualise some data in PowerPivot Chart.

This is my issue:
Given this basic dimensional model:
[Dim Store] --> [Fact Table] <--[Dim Product]

And supposing that:
1. The Fact table is 'Factless' as only counts number of sale events by store, product and vendor;
2. The Fact table has the degenerated dimension vendorID.
3. Vendors sell products in different stores.

How to build a PowerPivot chart to show the relation below?
total (count of) vendorsID that sold the product 'P1' in the store 'S1' who also sold the product 'P2' in the store 'S2'?

Can PowerPivot show me that?
Thanks,
Juan

 

[Laurent C]

If you define the following
[# P1 S1] : =COUNTROWS(FILTER(FactSales, FactSales[Product]="P1" && FactSales[Store] ="S1"))
[# P1 S2] : =COUNTROWS(FILTER(FactSales, FactSales[Product]="P2" && FactSales[Store] ="S2"))

You can try

=COUNTX(VALUES(FactSales[Vendor]); IF( [# P1 S1] >0 && [# P2 S2] >0;1;BLANK()))
or
=SUMX(VALUES(FactSales[Vendor]); IF( [# P1 S1] >0 && [# P2 S2] >0;1;0))

Edit: also check if the behaviour of the measure meets your expectations, when you add a store or a product as a filter / row / column.

 

[J F V]

If you define the following
[# P1 S1] : =COUNTROWS(FILTER(FactSales, FactSales[Product]="P1" && FactSales[Store] ="S1"))
[# P1 S2] : =COUNTROWS(FILTER(FactSales, FactSales[Product]="P2" && FactSales[Store] ="S2"))

You can try

=COUNTX(VALUES(FactSales[Vendor]); IF( [# P1 S1] >0 && [# P2 S2] >0;1;BLANK()))
or
=SUMX(VALUES(FactSales[Vendor]); IF( [# P1 S1] >0 && [# P2 S2] >0;1;0))

Edit: also check if the behaviour of the measure meets your expectations, when you add a store or a product as a filter / row / column.

Genious, thanks Laurent.