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:   COLUMN DESIRED OUTCOME (isolate all 'C') A. Red, A.Green, A. Violet, B. Pink, C. Brown C. Brown ...
Need Help
Tips and Tricks
v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

Can you please share a few screenshots?

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

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

      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's avatar
        v-xuding-msft
        Icon for Community Support rankCommunity Support
        6 years ago

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