Forum Discussion

praveen_k's avatar
praveen_k
Helper I
10 years ago

IF to select a column value

I am trying to use a IF statement to select a column. I have a measured column which calculates percentage change. When the percentage change (measure column) is greater than 0, ID column should be selected and viewed on report. eg- id total month measure 23 65 2 0 23 85 3 0.3 5 6 2 0 5 5 3 -0.16 2 10 2 0 2 11 3 0.1 When the measure column is -0.16, ID = 5 need to be displayed on the report.
Need Help

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      praveen_k I'm assuming you want to keep the measure column. And my other assumption is that you want to keep the measure if the value is greater than or equal to zero, and everything that is under should be the ID. Here is a calc column that will work for what you are asking. If this is not accurate, please clarify the discription.

       

      Value = IF('table'[measure] >= 0, 'table'[measure], 'table'[id])

       

      • Neuro81's avatar
        Neuro81
        Helper I
        10 years ago

        Hi Anonymous

        Just a quick note on your formula regarding best practise

        we should avoid putting the Table name next to the measure
        ref: top 5 best practise tips

        praveen_kAs Eno suggested your calc column should work, but I'd suggest appending the result with something so that the reader knows that the value returned is either the Id or the result of a mesaure

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    10 years ago

    Hi Praveen_k,

     

    According to your description, you need to display the ID which have measure value >0, right?

     

    If that is the case, you can create a calculated column to display if the current have measure value >0 or not. I have tested it on my local environment, here is the sample DAX expression below for you reference.
    Check = IF(IFfunction[measure]<0,1,0)
    Sciler = IF(CALCULATE(SUM(IFfunction[Check]),ALLEXCEPT(IFfunction,IFfunction[id]))>0,"<0",">0")

    Regards,

    Charlie Liao