Conditional column with does NOT include and OR criteria

[Anonymous]

Hello,

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

COLUMN	DESIRED OUTCOME (isolate all 'C')
A. Red, A.Green, A. Violet, B. Pink, C. Brown	C. 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!

[Greg_Deckler]

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]

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]

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"

[Anonymous]

Thanks,

unluckily seems not working in my scenario. I get a blank column.

[v-xuding-msft]

Hi @Anonymous ,

Can you please share a few screenshots?

[Anonymous]

Hi @v-xuding-msft ,

I manage to create my desired column with a few Pytonh lines, so that is OK now.

Still my 'problem' on how to filter that on the individual' attributes remains, since that even my 'desired' column (which is already filtered from the original, as pet my example) includes too many attribures per line (screenshot at the end).

I would need to filter that column to find e.g. all entry including 'N1 Barks'.

If I slplit the column that does not work as said since I end up with 60+columns, I tried a few smart slicers but it's they do not handle so many columns. 

For now the best solution I found is the 'Text Filter' visual which I downloaded from the MP, but that has limitations as well.

Any hint on how to best do this ?

Many thanks for your support!

[v-xuding-msft]

Hi @Anonymous ,

 I also tried other custom visuals, like Smart Filter,Attribute Slicer. However, I think it might be the best way to use the visual of Text Filter. 

And I found a blog that create a new table manually as a slicer to filter data. I don't know if it works for your situation. Maybe you can get some ideas from it.

https://blog.pragmaticworks.com/power-bi-problem-design-solution-text-search-slicer

[Anonymous]

Hi @v-xuding-msft ,

thansk for the link, that seems to be pretty much my case!

I followed the example and entered correctly tha DAX and everything, but when I add the new measure with the DAX to the table (and I should get a TRUE/FALSE values column) I get the following error instead: 

MdxScript(Model) (6, 9) Calculation error in measure 'Sheet1 (2)'[IsFiltered]: The function SUMX cannot work with values of type Boolean.

I think it's because the SUMX clearly expect numbers, while I have text data - which is in line with what suggested in the example of the video you linked.
Any hint ?

The full DAX is: 

IsFiltered = IF(
    SUMX(RawMat;
        (FIND
            (RawMat[Raw Materials];
            MAX('Sheet1 (2)'[Product Lev 1]);;0)) > 0)
            ;"True"
            ;"False") 

 Thansk again!!

[v-xuding-msft]

Hi @Anonymous ,

Sorry for late back. Have you resolved the problem? If not, please try like this:

IsFiltered =
IF (
    SUMX (
        RawMat;
        FIND ( RawMat[Raw Materials]; MAX ( 'Sheet1 (2)'[Product Lev 1] );; 0 )
    ) > 0;
    "True";
    "False"
)

[Anonymous]

Hi @v-xuding-msft ,

thanks - I just tried it. Still same problem, does not work because SUMX cannot work with boleeans. Strange since in the example in the video you shared with me, that is exactly what they seem to be doing....

[v-xuding-msft]

Hi @Anonymous ,

I create a new sample to test that formula. It works fine.  I think the error in your report might be caused by the position of parentheses. Maybe ">0" is contained in the function of SUMX. You could format it using the tool of DAX Formatter by SQLBI  firstly.  http://www.daxformatter.com/

And please share a few screenshots. We will understand clearly.