Forum Discussion
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!
| 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 |
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.
3 Replies
- rsbin
Community Champion
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,
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.
Thank you! This worked perfectly.
