Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Conditional column with does NOT include and OR criteria

Hello,

 

what is the best way to set up a conditional column in the following scenario:

 

COLUMNDESIRED OUTCOME (isolate all 'C')
A. Red, A.Green, A. Violet, B. Pink, C. BrownC. Brown
A.Green, A. Violet, C. Brown, C. White

C. Brown, C. White

B. Pink, B. Blue, A. Red

null

 

The pre-made conditional column function seems to be able to handle this (with an 'does not cointain).

I guess it is possible with a DAX formula?

 

This is a very simplified example, my column contain a very large number of attributes.

Thanks!

 

 

 

 

 

Need Help
Tips and Tricks

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Yeah, that's ugly. DAX is not the world's greatest text parser. So if that is really how your data looks, I would highly recommend using Power Query to split that column out and unpivoting it.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks, 

      yes it's ugly - with the addition that my data has many more attributes than the colours in my example (all in the same nasty format). If I split the column I end up with approx. 50 columns, too much too handle for any single slicer or smart slicer visual.

       

      Any hints? There has to be  a way to do this ....🤔

       

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi Anonymous ,

        You could have a try in Power Query like below.

        = Text.Combine(List.Select(Text.Split([COLUMN],","),each Text.Contains(Value.As(_,type text),"C.")),",")

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRTCEpN0VFw1HMvSk3NAzEUwjLzc1JLdBSc9BQCMvOydRSc9RScivLL85R0lODMWB2QZkxNMAVgVnhGZkkqki4kQZB+uAVAhlNOaSrYIKBzgDrySnNyoHaQ4EAdhKSzHsQgpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [COLUMN = _t, #"DESIRED OUTCOME (isolate all 'C')" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COLUMN", type text}, {"DESIRED OUTCOME (isolate all 'C')", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select(Text.Split([COLUMN],","),each Text.Contains(Value.As(_,type text),"C.")),","))
in
    #"Added Custom"