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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
rwoodward
Frequent Visitor

Pulling Text Data if Multiple Conditions Are Met DAX Help

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!

 

Column1Column2How it should look
EAYesYes
EANo 
EANo 
EBYesYes
EBYesYes
EBNo 
ECNoNo
ECNo 
ECNo 
EDYesYes
EDNo 
EENoNo
EENo 
EFYesYes
EFYesYes
EGYesYes
EHNoNo

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @rwoodward ,

I create a table and an Index column.

vyilongmsft_0-1738633741629.png

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 )

vyilongmsft_1-1738634000845.png

 

 

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @rwoodward ,

I create a table and an Index column.

vyilongmsft_0-1738633741629.png

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 )

vyilongmsft_1-1738634000845.png

 

 

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.

Thank you! This worked perfectly.

rsbin
Super User
Super User

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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