Forum Discussion

adnankabina's avatar
adnankabina
Helper II
10 months ago
Solved

logic on two column from different table

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 i...
Need Help
  • v-hjannapu's avatar
    v-hjannapu
    10 months ago

    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.


grazitti_sapna's avatar
grazitti_sapna
Super User
10 months ago

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's avatar
adnankabina
Helper II
10 months ago

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's avatar
    v-hjannapu
    Community Support
    10 months ago

    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.