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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    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.