Unexpected relationship/measure behavior-- Can some one explain

sreachard

New Member
Joined
Aug 14, 2012
Messages
17
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Scott,

The slicer doesnt do anything as the element used comes from the 'base' table, and what is on the pivot comes from the 'lookup' tables. by default, context propagates in the opposite direction (see more info here:Working with relationships in PowerPivot & SSAS BISM Tabular « Javier Guillén)

Regarding your measures, the difference has to do with context. For example, take business:g,cnum:c25,cname:michelle. [custCustct] yields a value of 1 as the distinctCount if executed over the customer table, which is the current level of your 'dimensional' data (cnum/cname). ([Business] does not slice customer data for the same reason above: it is in the base table, not the lookup.) .No value is derived for [polCustct],though, as there are no rows on the policy table with cnum:25 and cname:michelle at the business:g context.
 
Upvote 0
First, it seems to me that your table called dimPolicy might actually be a candidate to be the fact table, whereas factPolicy might be a dimension candidate (if each policyseq can only have one premium value, then premium value will actually be an attribute of the policy). If this is correct, then InfoceCurMonth should be linked to factPolicy table.

Returning to your issue, if you do COUNTDISTINCT(Table[ColumnName]), you will return a result for the whole table, not just the for the current selection.

COUNTDISTINCT(VALUES(Table[ColumnName])) would return the result for the current filter context.

Also note, that if you refer to the key of a dimension you do not event need to use COUNTDISTINCT. Doing COUNTROWS(VALUES(YourDimensionTable)) should be enough.
 
Upvote 0
Thanks to both of you! I've been reading the suggested links and playing aroung with this and though i had it figured out. I ended up creating measures using cross table filtering (Resolving Many to Many relationships leveraging DAX Cross Table Filtering « Gerhard Brueckl's BI Blog) I tried the methods in marco/alberto's articles and i did not see a measerable performance difference compared with the cross table filtering, The cross filtering is simpler to read/code so i went with that one.

Laurent correctly identified that my fact table was not really a fact table, i've added some more data to refelect that it really is a fact table with many to 1 not 1 to 1. I've also added a monthend date table and use the inforce as a bridge.

i have two measures to count customers
one counts the customer seq in the policy dimension, this works as i would expect (and want) it to.
CALCULATE(COUNTROWS(values(dimpolicy[customerseqid])),InforceCurMonth,business,Fact,MonthEnd)

The other one is identical except it counts in dimcustomer (snowflake from dimpolicy). This measure is counting customers in companies that had no premium for the period.
for the 2/25/2011 period customer Vikki has policy v4 in company v this is correct, but vikki also shows up under company f, she has a policy under company f but not for the selected timeperiod, so why is she being counted there.

http://sdrv.ms/QfXq1T

Hopefully some one can explain the behavior and how to get the desired behavior. I did try adding a filter on premium <> 0 but, as i expected it would, performance was not good.

Thanks
 
Upvote 0
My understanding is that you have 3 main dimensions (Business, customer, and time) and that you lack a proper dimension for Policy.
A single list of PolicySeq would be fine.
Your current dimPolicy table still acts as a lookup table for the 'Fact' table.
To clarify the following explanation let me rename your current dimPolicy as PolicyRelationship, and let me call the missing policy dimension NewPolicyDimension.
Then you could have a schema like that:
PolicyRelationship> dimCustomer
PolicyRelationship-> dimBusiness
PolicyRelationship-> NewPolicyDimension

Fact -> NewPolicyDimension

If I understood this correctly, the 'Fact' table is a description of what the policy contract covers. You might not need to relate this table to the business if this information is already contained in your 'ContractRelationship' table.
You could then just rewrite your expressions like this:
[# customers with an active contract] :=
=CALCULATE(
COUNTROWS( dimcust )
; CALCULATETABLE( PolicyRelationship;InforceCurMonth)
)
 
Upvote 0
Hi sreachard

You dont need to specify all the tables on the chain of relationships to propagate the filters. In other words, you can rewrite your formula as:

CALCULATE(COUNTROWS(values(dimcust[customerseqid])),Fact, InforceCurMonth )

and obtain the same result. This is because the other tables are indeed following the correct direction in which context propagates by default.

Regarding vikki showing up under company f for 2/25/2011, I checked your data and it makes sense she is appearing there.

From the first chain of relationships (to MonthEnd):

- dimcust[customerseqid] for vikki is 30
- dimpolicy has a match for this id, returning three policy seq: 10,11,12
- InforceCurMonth has a match for those ids, one of which is 'as of' of 2/25/2011

On the other chained relationship (business:

- dimcust[customerseqid] for vikki is 30
- dimpolicy has a match for this id, returning three policy seq: 10,11,12
- fact has two matches for those ids under businesseq: 3, 4
- business has a two matches for those ids under business: v and f

hence, mapping vikki to business f on 2/25/2011 would be the expected behavior
 
Upvote 0
Laurent,
Can you clarify what you mean by dimpolicy not being a proper dimension (i'm not being defensive, i just don't understand)? You are correct, the fact table has the premium and the coverages, the actual table has a coverage dimension, for the example i simplified it and put the coverage type in the fact. The underlying DW is a snowflake design and there are a lot of dimensions from policy(customer,producers, underwriters, geography), policy has a one to many relationship with the fact. Is a snowflake a bad practice in tabular?
 
Upvote 0
Javier,
i was reading this post last night MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering and i suspected i had more tables than i needed, thanks for confirming that!
I mostly get why she is showing up there, but I'm still confused on the second chained relationship, why is the dimpolicy=>inforcecurmonth relationship not there? I'm expecting that that relationship would remove policy seq 11 and 12. It is obviously working as you descibed it, but i'm struggling with why it's working that way.


Thanks

scott
 
Upvote 0
Choosing between a snowflake and a star schema will be a question of user-friendliness and performance. An extreme snowflake schema that would have all attributes as separate dimensions could still be ok.

My remark comes from this: Imagine you removed the 'Fact' table from your schema. Then your dimPolicy would very much look like a fact table with 1 dimension (customer) and 3 degenerate dimensions (policy, status, business).

Thus my feeling that your 'dimPolicy' table serves both as a fact table and as dimension for your 'Fact' table. This is something that can be done in PowerPivot and Tabular, but IMHO tends to obfuscate the model.
 
Upvote 0
ok I tried changing to a star

I can get a count of customers from the fact table but if i want to use the dimensions it is not working.

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.

link to workbook:http://sdrv.ms/QL5w33
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,784
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top