Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks
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.
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
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.
Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks
'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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |