One measure, two USERELATIONSHPs

[Russ Skinner]

In PowerPivot I have a ledger table related to a calendar table.

Relationship between the tables is Ledger[Paid Date] to Calendar [Date].

The ledger also contains a secondary date field [Bill Date]

I have successfully used the following measure to aggregate ledger outgoings by [Bill date] using the USERRELATIONSHIP function thus overriding the primary relationship.

=CALCULATE(sum(Ledger[Outgoings]),USERELATIONSHIP(Ledger[Bill Date],Calendar[Date]))

The ledger also contains a category field, CAT1, CAT2, CAT3 etc.

I'd like to be able to have a single measure that aggregates using the primary relationship for certain categories, but the secondary USERELATIONSHIP for others. I've attempted this using an If statement with no success.

Is there any way of achieving this?

Thanks,

Russ

 

[scottsen]

Certainly should be possible. I would think IF (ISFILTERED(Ledger[Cat1]), ...) sorta thing? Sometimes the location of the IF can get confused though (am i checking on a row level vs in aggregate), not sure what you tried of course.

Think I will need a bit more here though -- maybe what you tried that didn't work?