Forum Discussion
exclude certain text from a calculated column
Hi Anand24 Anonymous ,
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
Hi Anonymous ,
Can you share sample data and the expected output?
Regards,
HN
Anonymous 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.
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 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
UltimateParentDUNSNumber Total Value OD Group outcome NSA45476749 100 0-30 Elim-NSA67454 745 0-30 4569485 56873 0-30 56873 5738456 475834 0-30 475834 4735436 4564 0-30 4564