Conditional Column Based on Multiple Conditions

[kartiklal70]

Hi All, 

I have a CSR (Customer Search Ring) Table that is joined to a Milestones Table.  These two tables are joined on the CSR field. The data in the CSR table is as below: 

The data in the Milestones Table is as below:

Here's what I'd like to implement:

If Primary CSR YN = "No", then for that CSR, I'd like to get the Name, Actual Date and Forecast Date from it's corresponding Primary CSR for only Related Records "Site" and "SR".

For example, for CSR-1969, it's Name, Actual Date and Forecast Date in the Milestones table should be equal to that of CSR-0005 for Related Records "Site" and "SR". 

The issue is that when a CSR is NOT the Primary CSR, then its milestones will only have "CSR" Related Records. 

I'm not sure what the best solution for this would be. Would a Conditional Column in Power Query be possible or is it possible to achieve this via DAX? 

[Anonymous]

Hi @kartiklal70 ,

Not sure, please try:

Measure = 
VAR _CSR = 
CALCULATETABLE(
    VALUES('CSR Table'[CSR]),
    'CSR Table'[Primary CSR YN] = "No"
)
VAR _result = 
IF(
    MAX('Milestones Table'[CSR]) IN _CSR
    && MAX('Milestones Table'[Related Record]) IN {"Site","SR"},
    1
)
RETURN
_result

If I have misunderstood your requirements, please share the simplified example data and expected output.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[kartiklal70]

Hi @Anonymous ,

I'm not sure a measure would work here. I've shared a link to a sample pbix that might help.

https://www.dropbox.com/s/jhwjngdqe5utnt9/Sample%20Report%20Updated.pbix?dl=0

Here, when you drillthrough for CSR-1747 you get the following milestones (only its CSR related records). 

The Primary CSR for CSR-1747 is CSR-0007. When you drillthrough for CSR-0007 you get the following milestones (CSR, SR and Site Related Records)

Now, for CSR-1747 (the non-primary CSR), I'd like to get it's Site and SR milestones from its corresponding primary CSR (CSR-0007).