Unexpected relationship/measure behavior-- Can some one explain

[sreachard]

http://sdrv.ms/U9GUpN

I have a model with a fact table, policy dimension, customer dimension, and an inforcepolicy dimension, customer and inforce are snowflaked through policy.

The inforce dimension is monthend dates and policies that are active, but adding it as a slicer has no effect on the pivot???

I have 2 customer count measures, one is a distinctcount(dimpolicy[customerseqid]) the other is distinctcount(dimcustomer[customerseqid]) the two measures give different results, but there is a relationship between the two columns???? I thought the relationships behaved like joins, the measure in policy does seem to behave as such, but the measure using customer seems to behave like an outer join.

can some one explain why the measures behave differently and why the slicer does nothing?

Thanks

Scott

 

[JavierGuillen]

Hi Scott

Yes, policy seq 11 and 12 are eliminated for 2/25/2011, *but* the fact that policy seq 10 is still there instructs PowerPivot to show vikki under business f on that date. You can test this by removing the assoicated row in the 'inforce' linked table:

policyseq: 10
asOfDate: 2/25/2011

as soon as you refresh your powerpivot data and the pivot table, vikki (under business f for 2/25/2011) will be gone.

 

[Laurent C]

If i add a measure( calculate(DISTINCTCOUNT(dimcust[cstate]),Fact)) to count how many states each business had policies in for the selected time period it is still not working.

That is because filters on fact tables are not automatically propagated to other fact tables, just as in your many-to-many relationships. There are other patterns to force this propagation, but I prefer this one:
=CALCULATE(
DISTINCTCOUNT(dimcust[cstate]),
CALCULATETABLE(
Fact
,InforceCurMonth
)
)