Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

exclude certain text from a calculated column

Hi, I'm using the below dax to exclude the rows that contain certain text so that i could get the required value. Here's the dax: Column = IF( Sheet1[OD group] = "0-30", IF( Sheet1[Outcome] = "",

IF( not CONTAINS(Sheet1,Sheet1[UltimateParentDUNSNumber], "NSA"), Sheet1[Total Value] ) ))

 

But, I'm not getting the required outcome through dax. I still have to use the manual filters to get the value. Any help on getting the result through dax would be appreciated.

 

Thanks in advance.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anand24 @harshnathani ,

 

Thank you for your help with my query.

 

I've made minor changes to the DAX formula and now I can see the expected outcome. Here's what I used:

 

Column = IF(
AND(Sheet1[Days to Decision] = "0-30", NOT(CONTAINSSTRING(Sheet1[UltimateParentDUNSNumber],"NSA"))),
IF(Sheet1[Outcome] = "",
Sheet1[Total Value]))

 

Thanks again for your efforts!!!

 

Regards,

Mahesh

View solution in original post

7 REPLIES 7
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Can you share sample data and the expected output?

 

Regards,

HN

Anonymous
Not applicable

@harshnathani Hi, this is a confidential data and i do not have any sample data as such. However, I can give you a brief about it.

 

IF( Sheet1[OD group] = "0-30", IF( Sheet1[Outcome] = "",

IF( not CONTAINS(Sheet1,Sheet1[UltimateParentDUNSNumber], "NSA"), Sheet1[Total Value] ) ))

 

  • Total value consists of deal value
  • Outcome is blank
  • There are certain numbers under "UltimateParentDUNSNumber" which has "NSA" as part of the data in a cell
  • OD group is a calculated column created to determine the deal open days since it's first record

the outcome I'm anticipating is that I should exclude all the "NSA" listed data (regardless of the placing of the alphabets onin a cell) and only calculate the rest of the data based on "OD Group"

Hi @Anonymous ,

 

Let me know if this works.

 

1.jpg

 

Column = 
SWITCH(
    TRUE(),
    'Table'[OD group] = "0-30" && CONTAINSSTRING('Table'[UltimateParentDUNSNumber],"NSA"), 'Table'[Total Value],
    BLANK()
)

 

If not please create some sample data and share the output needed.

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

@harshnathani Thank you for sharing the DAX for the query. However, I'd the formula for not calculating the "NSA".

 

"UltimateParentDUNSNumber" contains Numeric and AlphaNumeric data. In AlphaNumberic, I should "Exclude" all the data which contains "NSA" in them.

 

The formula you've provided is calculating all the rows which contains "NSA" in them. This should be the otherway around. Below is the sample data on how it looks and the outcome I'm expecting.

 

Regards,

Mahesh

 

UltimateParentDUNSNumberTotal ValueOD Groupoutcome
NSA454767491000-30 
Elim-NSA674547450-30 
4569485568730-3056873
57384564758340-30475834
473543645640-304564

Hi @Anonymous ,

 

You can use the below DAX in a measure:

Group Outcome =
SWITCH(
TRUE(),
MAX('Table'[OD group]) = "0-30" && NOT(CONTAINSSTRING(MAX('Table'[UltimateParentDUNSNumber]),"NSA")), SUM('Table'[Total Value]),
BLANK()
)
 
Or you can use below DAX as well:
Group Outcome = IF(MAX('Table'[OD group]) = "0-30" && NOT(CONTAINSSTRING(MAX('Table'[UltimateParentDUNSNumber]),"NSA")), SUM('Table'[Total Value]),BLANK())
 
Here's the result:
notcontains.PNG
 
The 1st DAX is almost the same as @harshnathani has given in his previous comment but only with a NOT added to exclude "NSA".
 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

Anonymous
Not applicable

@Anand24 I've tried both the formulas provided and I'm seeing only one output with both the formulas. below is the screenshot of the output

 

Mahesh_Gupta_0-1604321872461.png

 

Anonymous
Not applicable

Hi @Anand24 @harshnathani ,

 

Thank you for your help with my query.

 

I've made minor changes to the DAX formula and now I can see the expected outcome. Here's what I used:

 

Column = IF(
AND(Sheet1[Days to Decision] = "0-30", NOT(CONTAINSSTRING(Sheet1[UltimateParentDUNSNumber],"NSA"))),
IF(Sheet1[Outcome] = "",
Sheet1[Total Value]))

 

Thanks again for your efforts!!!

 

Regards,

Mahesh

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.