DAX RELATED with a condition

[bsz412]

Hi, 

I have a 'sales' table where I have years months, and identifer. 

A 'ready_list' table is connected to this table: via another table, with one to many relationships: 

the ready_list table looks like this:

the date next to the id shows when the item became ready.

What I would need is a new column in the sales table, with the following logic:

if item was ready in the indicated 'sales' year, regardless of which month the item became ready, the new column (lets call it 'ready') should show "1". 

So in the example above, if item "74490....." is in the ready_list table, and it was ready in 2022 (so it became ready anytime in 2022 or before 2021) then value is "1" else 0.

second line: item "8013....." if this item is in the ready_list table, and it became ready anytime in 2021 or earlier, then value is "1" else 0.

How could I do this? I dont think I can add this year condition to the related function

[v-eqin-msft]

Hi @bsz412 ,

Since there are multiple tables, could you provide me with more details about your table in Table/Text format and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[jcalheir · Accepted solution]

Hi

I think you can do it with Related function. Try this:

Create a calculated column in the sales table with the following formula, and check if the values are correct:

Ready date = RELATED(ready_list[month_year])