cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeteD1302 Frequent Visitor
Frequent Visitor

DAX Direct Query - if text contains

I have a direct query where I need to add a new column; looking for a part of the text sting and reporting specifed string.

I have viewed a number of posts for IF and CONTAINS and none of the solutions work for a direct query.

e.g. For text string AF - NEW - 5567 if NEW is found then return NEW, if not then OLD

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
PeteD1302 Frequent Visitor
Frequent Visitor

Re: DAX Direct Query - if text contains

I have managed to find a solution from a search for"Re: Conditional Columns or IF Conditions in Direct Query mode" from 04-30-2017. The link as below

https://community.powerbi.com/t5/Desktop/Conditional-Columns-or-IF-Conditions-in-Direct-Query-mode/t...

With the resulting direct query as follows;

IF(Table[Name]="NEW"),"NEW", "OLD

This is for a New Column and helps to identify what are new and old items from a very large SQL server source

 

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: DAX Direct Query - if text contains

@PeteD1302,

 

Click File-> Options-> Direct Query-> Allow unrestricted measures in direct query mode.

Capture.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PeteD1302 Frequent Visitor
Frequent Visitor

Re: DAX Direct Query - if text contains

Jimmy Tao, I made the suggested change.

I am still having issue with correct DAX command, which is correct or none.

e.g.

IF (Text.Contains('Table'[Name], "NEW") then NEW else "OLD

or IF ('Table'[Name], Text.Contains ("NEW"), then NEW else OLD

or IF (CONTAINSROW('Table'[Name], "NEW") then"NEW" else OLD

or  IF ('Table'[Name], CONTAINSROW ("NEW") then NEW else OLD

 

Highlighted
PeteD1302 Frequent Visitor
Frequent Visitor

Re: DAX Direct Query - if text contains

I have managed to find a solution from a search for"Re: Conditional Columns or IF Conditions in Direct Query mode" from 04-30-2017. The link as below

https://community.powerbi.com/t5/Desktop/Conditional-Columns-or-IF-Conditions-in-Direct-Query-mode/t...

With the resulting direct query as follows;

IF(Table[Name]="NEW"),"NEW", "OLD

This is for a New Column and helps to identify what are new and old items from a very large SQL server source

 

 

Community Support Team
Community Support Team

Re: DAX Direct Query - if text contains

@PeteD1302 ,

 

In DAX, it should be:

Measure =
IF ( CONTAINS ( Table, Table[Name], "NEW" ), "NEW", "OLD" )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.