Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kartiklal70
Frequent Visitor

Conditional Column Based on Multiple Conditions

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: 

 

kartiklal70_0-1671623774414.png

The data in the Milestones Table is as below:

kartiklal70_1-1671623995106.png

 

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. 

kartiklal70_2-1671624899794.png

 

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? 

 

 

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @kartiklal70 ,

 

Please try:

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

vjianbolimsft_0-1672105864529.png

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
    )

vjianbolimsft_1-1672105953211.png

Final output:

vjianbolimsft_2-1672105980317.png

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.

View solution in original post

6 REPLIES 6
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1672281594989.png

Final output:

vjianbolimsft_1-1672281617941.png

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

@v-jianboli-msft ,

 

Thank you! That works perfectly. 

v-jianboli-msft
Community Support
Community Support

Hi @kartiklal70 ,

 

Please try:

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

vjianbolimsft_0-1672105864529.png

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
    )

vjianbolimsft_1-1672105953211.png

Final output:

vjianbolimsft_2-1672105980317.png

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.

@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? 

kartiklal70_0-1672225833696.png

 

v-jianboli-msft
Community Support
Community Support

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.

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.