Forum Discussion

luannk's avatar
luannk
Regular Visitor
8 years ago

Cannot use a dimension table & its primary keys as arguments in ALLEXCEPT()

Hi,   My goal is: creating a pivot table which presents cumulative total amount of all transactions labeled as "Principal Repayment", up to a certain point in time. This point in time is specified ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

When you are testing both your measures, is the column that is providing row context also change to keep up with the measure?

 

I ask because in your first measure, your ALLEXCEPT is on Transaction Table, Currencies Field.  In the second measure it is now on the Currencies Table, Currencies Field.  If you have simply taken your new measure and placed it into the same context as the previous measure the ALLEXCEPT.

I also notice that one of your filters is based on a date in the Transaction table, however in the 2nd measure you are no longer doing an ALL on that table. 

 

I'd also recommend changing your first measure to avoid using the FILTER function and use this type of structure:

Cumulative Principal Repayment = VAR filterDate = MAX('Transaction'[Transaction Date])
RETURN
CALCULATE(
	SUM('Transaction'[Amount]),
	ALLEXCEPT('Transaction', 'Transaction'[Currencies]),
	'Transaction'[Transaction Date] <= filterDate,
	'Transaction'[Label] = "Principal Repayment"
)

Second Measure:

Cumulative Principal Repayment = VAR filterDate = MAX('Transaction'[Transaction Date])
RETURN
CALCULATE(
	SUM('Transaction'[Amount]),
	ALLEXCEPT('Currencies', Currencies[Currencies]]),
	'Transaction'[Transaction Date] <= filterDate,
	'Transaction'[Label] = "Principal Repayment"
)