Forum Discussion
Create a measure that only filters based on 2 fields
I have a table visual that has 7 columns from various different connected tables in PBI showing order history. I want to create a measure that shows the last time an item on a row was shipped to the customer at that same row but I want it to ignore the other columns in the visual as currently adding a max date measure only shows the date of that row and not the actual latest ship date. Any workarounds?
7 Replies
- amitchandak
Super User
George375 , Not very clear, see if these can help
Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.Here is an example of what my situation is. Basically I have a log of historical data with order #, transaction date, item code and customer number. I want on each line a measure that shows the latest date that item went to that customer. But becuase the order # and date field are in the visual, the measure filters only on those dates when in the table so shows the undesired dates as seen on the example table. Want to know what dax would help me get a table like my goal and not what im currently getting
Goal:
Order Number Transaction Date Item Code Customer Number Latest Date 1 1/1/2022 ABC 123 1/4/2022 2 1/2/2022 ABC 123 1/4/2022 3 1/3/2022 ABC 123 1/4/2022 4 1/4/2022 ABC 123 1/4/2022 What Im getting on PBI:
Order Number Transaction Date Item Code Customer Number Latest Date 1 1/1/2022 ABC 123 1/1/2022 2 1/2/2022 ABC 123 1/2/2022 3 1/3/2022 ABC 123 1/3/2022 4 1/4/2022 ABC 123 1/4/2022
- bolfri
Solution Sage
Last transaction date = var this_order = 'Sample'[Order Number] var this_customer = 'Sample'[Customer Number] return CALCULATE( MAX('Sample'[Transaction Date]); 'Sample'[Order Number] = this_order; 'Sample'[Customer Number] = this_customer )Result:
Is that what you meant?
I need it to show 01.04.2022 for all as that was the last time it was shipped
- bolfri
Awww I am sorry, I didn't notice that. So you want a MAX(transaction date) for Customer Number?
Max transaction date per customer = var this_customer = [Customer Number] return CALCULATE( MAX([Transaction Date]); FILTER(Tabela;[Customer Number] = this_customer) )
