Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I'm trying to create a calculated column in one of my tables that says:
IF( row CONTAINS "A", put "A", otherwise put "B")
The problem is I can't figure out what the contains function is in DAX, and I've looked everywhere. Can anyone help me out?
Thanks in advance.
Solved! Go to Solution.
The easiest way to do this would be to do the column in the query rather than the resulting data model table. Hit the Add Custom Column there and the code would be
if Text.Contains([ColumnName], "A") then "A" else "B"
If you want to do it in DAX it's a bit more messy. My best idea is to search for the position of the text you want to find, and check for that to return an error when the text is missing.
CustomColumn = IF( ISERROR( SEARCH("A", TableName[ColumnName]) ), "A", "B" )
Proud to be a Super User!
I think the expression was written wrong.
it should be
CustomColumn = IF( ISERROR( SEARCH("A", TableName[ColumnName]) ), "B", "A" )
Or
CustomColumn = IF( IFERROR( SEARCH("A", TableName[ColumnName]) , -1) > -1, "A", "B" )
Please correct me if I am wrong.
I need a formua to create a new column that checks a exising column in a table and provides a new value based on multiple condtions.
The formula shall find specified text contained in a longer text string, the searched text can be at the beginning the end or the end of the string.
If the searched text combination is identified, a new text should be put in the new column field.
Logic:
check field if somewhere in the text string "*abc* " is contained
if contained, put "xyz"
if not contained
check if " *def* " is contained, if contained, put "*ufw*" in the new colums
if non of the searched combinations is identified, put "hij" in the new column
many thanks!!
Hi,
Given the data below, i wrote the following calculated column formula in the first Table. You may download my solution workbook from here.
=LOOKUPVALUE(keywords[Result],keywords[Keywords],FIRSTNONBLANK(FILTER(VALUES(keywords[Keywords]),SEARCH(keywords[Keywords],Data[Data],1,0)),1))
I solved a similar scenario with the help of nested IF and OR
RANK = IF(OR(STUDENT[GRADETYPE] = "VERY GOOD", STUDENT[GRADETYPE] = "GOOD"),"One" , "Two")
@satish I think you're right. Looks like I got A and B backwards. But I'd go with your first suggestion. IFERROR introduces unnecessary complexity to the formula in my opinion.
Proud to be a Super User!
IFERROR does add complexity.
In my case I had multiple conditions combined so I had to use IFERROR and I thought someone else might it need it too.
@satish yeah, definitely never hurts to have an alternative method for similar cases.
Proud to be a Super User!
Hello
Is there a similar DAX to search for Text within different tables?
Thanks
I had to nest it a few times - that worked perfectly. Thanks for being so specific with the code too, that really helped. Much appreciated!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |