Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lg_analyst
Helper I
Helper I

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: 

lg_analyst_0-1656012586395.png

 

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:

lg_analyst_1-1656012844283.png

 

ORDERS table:

lg_analyst_2-1656012858783.png

 

Thank you very much

 

2 REPLIES 2
amitchandak
Super User
Super User

@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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.