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.
In Power BI, I'm trying to create a new column (with DAX) that extracts text to the left of a dash ("-"), based on an existing column.
ColumnA | CalculatedColumn |
AAA - 789D | AAA |
AAA - DWOS | AAA |
BBB - 8F WX | BBB |
BBB - 5RS | BBB |
CC - YXDZ | CC |
Please note, the data is connected using DirectQuery so I've been running into some roadblocks using DAX.
Any help would be appreciated!
Try this:
calculcatedColumn = LEFT([ColumnA], SEARCH("-", [ColumnA]) - 1)
The idea is that the dax will grab everything to the left (excluding the space) of the first "-" character in each row.
Thanks, vicky_
Unforunately, that formula results in this error message for me:
Function 'SEARCH' is not allowed as part of calculated column DAX expressions on DirectQuery models.
Thanks, vicky_!
Using all 4 arguments with the SEARCH function works great when linked to a DirectQuery model!
ex., SEARCH(" -",[ColumnA],1,0)
Unfortunately, I can't get the LEFT function to work with it (but I can get the LEFT function to work alone, if I use a single number as the <num_chars>, ex., = LEFT([ColumnA],3; unforunately that's not dynamic enough for my needs).
I imagine it is because I am not presented an option to "Allow unrestricted measures in DirectQuery mode" (even though I am using power bi desktop) as shown in the thread you linked:
I have tried the following with no luck (they all give an OLE DB or ODBC expression error):
CalculatedColumn = LEFT([COLUMNA], SEARCH("-", [COLUMNA],1,0) - 1)
CalculatedColumn = LEFT([COLUMNA],(SEARCH("-", [COLUMNA],1,0)-1))
CalculatedColumn = LEFT([COLUMNA],FIND("-",[COLUMNA],1,0))
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |