Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 |
Solved! Go to 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.
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.
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,
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
62 | |
46 | |
45 |