Forum Discussion
Calculating column based on filter on another table
Trying to simplify my model as much as possible:
PRODUCTS table has a list of product_ids (which can be replicated), a season and a start and end date for the season
ORDERS a list of orders, product_ids purchased, quantity and order_date
DATE MASTER a calendar of the current and past year
Ther is a One to Many relationship between DATE MASTER(Date) and ORDERS(order_date)
On the PRODUCTS table I have this calculation:
Where
Max Filtered Date = MAX('DATE MASTER'[Date])
I'd like prod_quantity to be dynamic and change when a different date is selected from the date filter. In my example I set 31/07/2022 as max date and I'd like prod_quantity to return 3. I also tried creating a metric instead of the column but still can't get this to work. Is there a way to solve this?
Output:
ORDERS table:
Thank you very much
2 Replies
lg_analyst , No you can not use slicer value in a calculated column. You need to create a measure .
Move this calculation inside the expression part of Sumx , minx or countx
Sumx(Order, <Your code>)
Hi amitchandak thank you for replying. I can't use the formula you suggest because, as you can see from the first screenshot, I'm summing data in one table (PRODUCTS) but based on filters from that and another table (ORDERS). Can you suggest a way to work around this?
Thank you
