cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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
Super User III
Super User III

Hi @Mahesh_Gupta ,

 

Can you share sample data and the expected output?

 

Regards,

HN

@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 @Mahesh_Gupta ,

 

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)

@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 @Mahesh_Gupta ,

 

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 !!!

@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

 

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors