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? 

[v-jianboli-msft]

Hi @kartiklal70 ,

Do you want the CSR column to be displayed uniformly in the final drillthrough results when the drillthrough object is not a Primary CSR?

If so, please try:

First create a measure:

New CSR = 
var _a = SELECTEDVALUE(SR[CSR])
return IF(_a in SELECTCOLUMNS('Milestones2',"Primary CSR",[Primary CSR]),MAX('Milestones2'[CSR]),_a)

Then use the measure to replace the original CSR:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kartiklal70]

@v-jianboli-msft ,

Thank you! That works perfectly. 

[v-jianboli-msft · Accepted solution]

Hi @kartiklal70 ,

Please try:

First Duplicate the Milestones table and delete the relationship between the new table and SR:

Then use the new table to create a table visual in page 2

Apply the measure to the visual level filter:

Measure =
VAR _a =
    SELECTEDVALUE ( 'SR'[CSR] )
VAR _b =
    SELECTEDVALUE ( SR[Primary CSR YN] )
VAR _c =
    SELECTEDVALUE ( SR[Primary CSR] )
RETURN
    SWITCH (
        TRUE (),
        _b = "Yes"
            && MAX ( 'Milestones2'[CSR] ) = _a, 1,
        _b = "No"
            && (
                MAX ( 'Milestones2'[CSR] ) = _c
                    && MAX ( 'Milestones2'[Related Record] ) IN { "Site", "SR" }
            ), 1,
        _b = "No"
            && MAX ( 'Milestones2'[CSR] ) = _a, 1
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kartiklal70]

@v-jianboli-msft ,

Thank you for that. That's very helpful and I'm almost at the solution I need!

The only thing that needs to be updated now is that when we drllthrough for a non primary CSR (CSR-1969 in this case), we see CSR -1969 instead of CSR-0005 in the CSR column for Site/SR related records. 

Any ideas on how to do that? 

[v-jianboli-msft]

Hi @kartiklal70 ,

If Primary CSR YN = "Yes", then what should it look like? What does the "site" refer to?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kartiklal70]

@v-jianboli-msft ,

Please see link for a sample file. 

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

If Primary CSR = "Yes", then it's milestones will have all the fields we care about and we do not need to make any change. 

When, Primary CSR = "No", it's milestones will only have then it will only have CSR-type milestones (see field Related Record) – its SR and Site type milestones are those in the corresponding primary CSR. 

Might be easier to understand once you see the sample data. Here, the primary CSR for CSR-1969 is CSR-005. When we do a drillthrough from Page 1 to Page 2 for CSR-1969, we only see it's "CSR" type milestones (refer to field Related Record), it's SR and Site type milestones are those of CSR-005 and I need to see these same milestones for CSR-1969. 

Hopefully, this makes more sense. Any help would be appreciated!