cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
luannk Visitor
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
Ross73312 Super Contributor
Super Contributor

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

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

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 292 members 3,070 guests
Please welcome our newest community members: