Pulling Text Data if Multiple Conditions Are Met DAX Help

[rwoodward]

I am working with text data and need help creating the DAX. Using the example data below. I am looking to create a DAX that gives me the text data in Column2 under these conditions: If Column1 has duplicated data, then I only want to copy the data in Column2 for one of rows leaving the other rows blank UNLESS Column2 equals "Yes" than I ALWAYS want to pull the Yes data as many times as it comes up. I created a 3rd row to show how I would like the data to look with the appropriate DAX.

Please help!

Column1	Column2	How it should look
EA	Yes	Yes
EA	No
EA	No
EB	Yes	Yes
EB	Yes	Yes
EB	No
EC	No	No
EC	No
EC	No
ED	Yes	Yes
ED	No
EE	No	No
EE	No
EF	Yes	Yes
EF	Yes	Yes
EG	Yes	Yes
EH	No	No

[Anonymous · Accepted solution]

Hi @rwoodward ,

I create a table and an Index column.

Then I create a calculated column and here is the DAX code.

Column = 
VAR _Column =
    IF (
        'Table'[Column2] = "Yes",
        "Yes",
        IF (
            'Table'[Column2] = "No"
                && CALCULATE (
                    COUNTROWS ( 'Table' ),
                    ALLEXCEPT ( 'Table', 'Table'[Column1] ),
                    'Table'[Column2] = "Yes"
                ) = 0,
            "No",
            BLANK ()
        )
    )
VAR RankNo =
    RANKX (
        FILTER (
            'Table',
            'Table'[Column1] = EARLIER ( 'Table'[Column1] )
                && _Column = "No"
        ),
        'Table'[Index],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( _Column = "No" && RankNo > 1, BLANK (), _Column )

Best Regards

Yilong Zhou

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

[rwoodward]

Thank you! This worked perfectly.

[rsbin]

@rwoodward ,

I would go about this in a slightly different way.

You can use Power Query to delete your Duplicate Rows.

In PQ, select both column then use the drop down for Remove Rows and select Remove Duplicates.

If you need to do any additional transformations, either in PQ or DAX, it will be much easier to deal with.

Hope this helps.

Regards,