logic on two column from different table

[adnankabina]

I have two table joined with PO and DC columns both exists in both table and created a relationship. I have YYYYWK 202534 in first table and YYYYWK  202545 from second table. I want to put condtion if yearweek from one table is greater than year and week from second table then flag as live 

yr_wk from first table and yr_wk_tracker from second table 

I used this dax 

YearWeekStatus =

VAR RelatedYearWeek = RELATED('Projects Overview'[YR_WK_TRACKER])

RETURN

    IF(

        code[YR_WK] > RelatedYearWeek,

        "Live",

        "Not Live"

    )

But I am getting this error 

!The column 'Projects Overview[YR_WK_TRACKER]' either doesn't exist or doesn't have a relationship to any table available in the current context.

[v-hjannapu]

Hi @adnankabina,

I would also take a moment to thank @grazitti_sapna  , @SwarnaTeja  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @adnankabina,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[grazitti_sapna]

Hi @adnankabina,

RELATED only works if there is a single-valued relationship from the current table to the related table (1-to-many or many-to-1)

Try Below DAX

YearWeekStatus =
VAR RelatedYearWeek =
LOOKUPVALUE(
'Projects Overview'[YR_WK_TRACKER], -- column to return
'Projects Overview'[PO], code[PO], -- match PO
'Projects Overview'[DC], code[DC] -- match DC
)
RETURN
IF(
code[YR_WK] > RelatedYearWeek,
"Live",
"Not Live"
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[adnankabina]

hi actually it is dc to dc and zone to zone.

I tried this but getting this error.

YearWeekStatus =

VAR RelatedYearWeek =

LOOKUPVALUE(

'Projects Overview'[YR_WK_TRACKER], -- column to return

'Projects Overview'[Zone ], code[ZONE_2],

'Projects Overview'[DC], code[DC]

 -- match DC

)

RETURN

IF(

code[YR_WK] > RelatedYearWeek,

"Live",

"Not Live"

)

error : A table of multiple values was supplied where a single value was expected.

[v-hjannapu · Accepted solution]

Hi @adnankabina,

I have tried to reproduce your scenario in Power BI and used sample data with Zone and DC combination. The DAX has been modified to use MAX() inside CALCULATE so it works even when there are duplicate Zone–DC rows.

The result now flags as Live when the YR_WK is greater than the tracker week, Not Live otherwise, and No Tracker when there’s no matching record.

I’m attaching a sample PBIX file for your reference  you can check the DAX and visuals for better understanding.

Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Harshitha.

[SwarnaTeja]

Hi @adnankabina ,

Please check if the relationship is not many to many becasue of which the Related function will not work.