Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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"

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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

     

    UltimateParentDUNSNumberTotal ValueOD Groupoutcome
    NSA454767491000-30 
    Elim-NSA674547450-30 
    4569485568730-3056873
    57384564758340-30475834
    473543645640-304564
    • Anand24's avatar
      Anand24
      Super User
      5 years ago

      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:
       
      The 1st DAX is almost the same as Anonymous 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's avatar
        Anonymous
        Not applicable
        5 years ago

        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