CODEWITHSUNDEEP
powerbidax
Bollasi97
Bollasi97

Reputation: 53

Get min date from another table with filter

I got two tables. There is a relationship between id and user id column. I want to add a calculated column in the user table with the first transaction of the user, for invoice type 'A'.

When I use: Calculate(min(Transaction[transaction date])) it works fine, but I need to filter on invoice type. When I use Calculate(min(Transaction[transaction date]),Filter(Transaction,Invoice type="A")) I only get 2021-02-01 and the realtionship does not work.

What is the most efficent way to achieve this?

User table:

ID Name
1 John Smith
2 Alan Walker

Transaction table:

user id transaction date Invoice type
1 2021-02-01 A
1 2021-02-25 A
1 2021-02-25 B
2 2021-03-05 A
2 2021-01-23 B

Upvotes: 1

Views: 4795

Answers (1)

Marcus
Marcus

Reputation: 3995

Here is the correct DAX code for your calculated column, just drop the FILTER statement, since that changes your filter context within the CALCULATE to look at all rows where Invoice type = "A", regardless of User ID.

Minimum Date = 
CALCULATE (
    MIN ( 'Transaction'[transaction date] ),
    'Transaction'[Invoice type] = "A"
)

Since you need context transition to provide you the row context from the Users table to the Transactions table, you can alternatively use this sort of filtering statement, where the table you are filtering on is also provided within the current filter context of your row in Users:

Min Date = 
CALCULATE ( 
    MIN ( 'Transaction'[transaction date] ) , 
    FILTER ( 
        CALCULATETABLE ( 'Transaction' ) , 
        'Transaction'[Invoice type] = "A"
    )
)

Upvotes: 1

Related Questions