extracting column info from tables in variables

[CL7777]

Hello all,

I need some help,

I have a rental table (call it 'rental transactions') that has rental data including item #, due date, and location. I have a 2nd table with a list of items (call it 'items'). There is a one to many relationship between the item #s in the 'item' table and the item #s in the 'rental transactions' table.

In the 'items' table, I want to create a calculated cloumn that goes into the 'rental transactions' table and finds all the item #s that correspond to the item # in the current row of the 'items' table, I want to return the location of that item # for the earliest due date in the 'rental transactions' table.

I have tried multiple things, but none seem to work. If I create a variable with a table that has the lines with the item # I want, I cannot access the columns in the table variable to get the earliest due date.

any help you could offer would be appreciated.

[amitchandak · Accepted solution]

 Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks

[VasTg · Accepted solution]

@CL7777 

It would have been easier if you have provided sample data. But here you go.

Item

Item

1

2

Rental Transaction Table

Item	Due Date	Location
1	01/01/2019	A
1	02/01/2019	B
2	08/01/2019	C

DAX for new column

Column =

VAR MIN_DUE_DATE = CALCULATE(MIN('Rental Transaction'[Due Date]),FILTER('Rental Transaction','Rental Transaction'[Item]='Item'[Item]) )

RETURN CALCULATE(MAX('Rental Transaction'[Location]),FILTER('Rental Transaction','Rental Transaction'[Due Date]=MIN_DUE_DATE))

If this helps, mark it as a solution.

Kudos are good too.

[VasTg · Accepted solution]

@CL7777 

It would have been easier if you have provided sample data. But here you go.

Item

Item

1

2

Rental Transaction Table

Item	Due Date	Location
1	01/01/2019	A
1	02/01/2019	B
2	08/01/2019	C

DAX for new column

Column =

VAR MIN_DUE_DATE = CALCULATE(MIN('Rental Transaction'[Due Date]),FILTER('Rental Transaction','Rental Transaction'[Item]='Item'[Item]) )

RETURN CALCULATE(MAX('Rental Transaction'[Location]),FILTER('Rental Transaction','Rental Transaction'[Due Date]=MIN_DUE_DATE))

If this helps, mark it as a solution.

Kudos are good too.

[amitchandak · Accepted solution]

 Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks

[CL7777]

'rental transactions' table

item #     due date    location

1                1/1/18        TX

1                6/6/17        MA

2                6/1/17        CO

2                7/1/19        AZ

3                9/1/16        CA

3                10/1/18      TX

'items' table

item #     column that I want to return (containing the location of the item at its earliest transaction date)

1                  MA

2                  CO

3                  CA

I want to return the "column that I want to return" table in above example. I dont know how to create a dax formula to do this. I have tried several things.

[amitchandak · Accepted solution]

Create these 2 has new columns in item

Min Due Date = Minx(filter(rental transactions,rental transactions[item_id]='Item'[Item ID]),rental transactions[due Date]) 
Max Location = MAXX(filter(rental transactions,rental transactions[due Date]='Item'[Min Due Date ] 
&& rental transactions[item_id]='Item'[Item ID]),rental transactions[location])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601