Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luannk
Regular Visitor

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 by the users in form of a slicer or column of the pivot table. The transctaions occur in many currencies, so the pivot table must distinguish the cumulative amount by currencies

 

My data:

  1. a dimension table named 'Currencies', its primary key: 'Currencies'[Currencies]
  2. a fact table named 'Transaction', it contain a column named 'Transaction'[Currencies]. There's an active "one to many relationship " between 'Currencies'[Currencies] and 'Transaction'[Currencies]

My 1st measure:

 

 

Cumulative Principal Repayment :=
CALCULATE (
    SUM ( 'Transaction'[Amount] ),
    FILTER (
        ALLEXCEPT ( 'Transaction', 'Transaction'[Currencies] ),
        AND (
            'Transaction'[Transaction Date] <= MAX ( 'Transaction'[Transaction Date] ),
            'Transaction'[Label] = "Principal Repayment"
        )
    )
)

This measure uses the foreign key ('Transaction'[Currencies]), and when I create a pivot table, using 'Transaction'[Currencies] as column, the results is as expected

 

The problem: if I rewrite the 1st measure by replacing 'Transaction'[Currencies] with 'Currencies'[Currencies], the measure returns an error.

 

My modified measure:

Cumulative Principal Repayment :=
CALCULATE (
    SUM ( 'Transaction'[Amount] ),
    FILTER (
        ALLEXCEPT ( 'Currencies', Currencies[Currencies] ),
        AND (
            'Transaction'[Transaction Date] <= MAX ( 'Transaction'[Transaction Date] ),
            'Transaction'[Label] = "Principal Repayment"
        )
    )
)

Since there are other measures must be filtered simultaneously with 'Cumulative Principal Repayment" by Currencies, I cannot use the first measure.

 

Thank you for your help,

 

1 REPLY 1
Anonymous
Not applicable

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"
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.