cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CL7777
Helper III
Helper III

extracting column info from tables in variables

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.

 

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

VasTg
Memorable Member
Memorable Member

@CL7777 

 

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

 

Item

Item
1
2

 

Rental Transaction Table

ItemDue DateLocation
101/01/2019A
102/01/2019B
208/01/2019C

 

 

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.

Connect on LinkedIn

View solution in original post

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
VasTg
Memorable Member
Memorable Member

@CL7777 

 

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

 

Item

Item
1
2

 

Rental Transaction Table

ItemDue DateLocation
101/01/2019A
102/01/2019B
208/01/2019C

 

 

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.

Connect on LinkedIn

View solution in original post

amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

'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.

 

 

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!