Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

IFCONTAINS()

Hey guys,

I have got a unique problem. I have got a column of values that sometimes return 0(it should be like that), however when it returns zero, I want to display the message in the report. Ive got it working with the following formula : 

Measure 35 = IF(CONTAINS('Krauti Kalij Traukiniai';'Krauti Kalij Traukiniai'[Value];0); " PROBLEM"; BLANK()) . However, now before the problem, I would like to return the index that was in the exact same row as that 0. Any ideas how can I  do that?
Need Help
Show and Tell
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    "CONTAINS"will check whether the column have the value,if yes,return true(),otherwise false(),see below:

    if "0"is a whole numer,I would suggest you use below dax expression:

    Column = IF('Krauti Kalij Traukiniai'[Value]=0,'Krauti Kalij Traukiniai'[Index]&" "&"Problem",BLANK())

    And you will see:

     

    if "0" is text,I would suggest you take below dax expression:

    Column = IF(FIND("0",'Table'[value],1,0)=1&&LEN('Table'[value])=1,'Table'[index]&" "&"Problem",BLANK())

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Be more specific. What do you mean by index?  DAX doesn't have a concept of row numbers.

    Generally you cannot return more than one result with a DAX function. One possible workaround is to use CONCATENATEX()

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous , as lbendlin said you cannot return more than one row in a measure without an aggregation function (such as MAX, SUM, or even SELECTEDVALUE)

     

    Assuming you have a column for [Index] you could use CONCATENATEX or SELECTEDVALUE with the Table[Index] column, for example: 

     

    Measure 35 = IF(CONTAINS('Krauti Kalij Traukiniai';'Krauti Kalij Traukiniai'[Value];0); CONCATENATEX(Table, Table[Index]) & " PROBLEM"; BLANK())

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    "CONTAINS"will check whether the column have the value,if yes,return true(),otherwise false(),see below:

    if "0"is a whole numer,I would suggest you use below dax expression:

    Column = IF('Krauti Kalij Traukiniai'[Value]=0,'Krauti Kalij Traukiniai'[Index]&" "&"Problem",BLANK())

    And you will see:

     

    if "0" is text,I would suggest you take below dax expression:

    Column = IF(FIND("0",'Table'[value],1,0)=1&&LEN('Table'[value])=1,'Table'[index]&" "&"Problem",BLANK())

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!