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( 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.

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Can you share sample data and the expected output?

     

    Regards,

    HN

    • 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)