Forum Discussion
Filter All Except with two fields in different tables but only for the first date in selection
Hi All,
I have an interesting challenge. I have a field called opening balance which used filter all except and sums the amount for the first date in selection from the transactions table. Now I want to filter based on fund_id which is in the fund table. i can add a second all except but i need to keep the first date in selection (the 'Transactions'[Trx_Date] < MIN ( '[Trx_Date]) part. When i try to add it in the second all except it gives me an error. How do I add another all except but still filter both all excepts on the first date from the transactions table.
My current formula
What I want to get
Hi Anonymous
Providing Fund is a dimension that has a relationship with Transactions, you could write you expression like below.
Measure = VAR __minDate = MIN ( 'Transactions'[Trx_Date] ) RETURN CALCULATE ( SUM ( 'relational COA_Transactions'[Trx_Amount_Adj] ), 'Transactions'[Trx_Date] < __minDate, ALLEXCEPT( 'Transactions' ,'Transactions'[COA L1 Code], 'Fund'[Fund_ID] ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
2 Replies
- Mariusz
Community Champion
Hi Anonymous
Providing Fund is a dimension that has a relationship with Transactions, you could write you expression like below.
Measure = VAR __minDate = MIN ( 'Transactions'[Trx_Date] ) RETURN CALCULATE ( SUM ( 'relational COA_Transactions'[Trx_Amount_Adj] ), 'Transactions'[Trx_Date] < __minDate, ALLEXCEPT( 'Transactions' ,'Transactions'[COA L1 Code], 'Fund'[Fund_ID] ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.