Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Change column based on measure value

Hi everyone,

 

I know you have an idea what can be the problem, and how to solve it.

The situation is the following:

I have created a measure based on filter value. DAX:

 

 

 

Measure = IF(FILTERS('TABLE_1'[column_1]) = "1", TRUE(), FALSE())

 

 

 

This measure works. If I change the filter, the value updates correctly.

 

I have a table (TABLE_2) that contains two column, lets call it "A" and "B". I want to create a new column ("C"). This column should be the same with "A" or "B" columns, based on my measure. DAX:

 

 

CreatedCol = IF([Measure] = TRUE(), TABLE_2[A], TABLE_2[B])

 

 

And it comes with a following error: "A table of multiple values was supplied where a single value was expected."

If I update the DAX of the new column to 

 

 

CreatedCol = IF(TRUE() = TRUE(), TABLE_2[A], TABLE_2[B])

 

 

or

 

 

CreatedCol = IF(FALSE() = TRUE(), TABLE_2[A], TABLE_2[B])

 

 

, it works.

 

So both the measure and the column work separately, but when I combine them, it breaks.

I hope you can save me.

Thanks in advance!

Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    You cannot create column "C" based on your measure's value. 

    Calculated columns are only calculated when you first define them and during a dataset refresh, it will not change by filters, it is a definite value, if you expect the value changes with filter, you can only use a measure.

     

    In a measure, columns cannot be referenced directly, MAX function in a measure can return the current value in the same row of the column.

     

    You can learn more about measures and calculated column in this article: calculated-measures-vs-calculated-columns

     

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , In DAX measure you need to have aggregation 

    CreatedCol = IF([Measure] = TRUE(), Max(TABLE_2[A]), max(TABLE_2[B]))

or

CreatedCol = IF([Measure] = TRUE(), sum(TABLE_2[A]), sum(TABLE_2[B]))

     

     

    Also , you can not use measure in a column

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Dear amitchandak,

      thank you for your answer!

      So as I understand if "TABLE_2[A]" and "TABLE_2[B]" are string columns, it is not possible to create column "C" based on my measure's value?

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

        You cannot create column "C" based on your measure's value. 

        Calculated columns are only calculated when you first define them and during a dataset refresh, it will not change by filters, it is a definite value, if you expect the value changes with filter, you can only use a measure.

         

        In a measure, columns cannot be referenced directly, MAX function in a measure can return the current value in the same row of the column.

         

        You can learn more about measures and calculated column in this article: calculated-measures-vs-calculated-columns

         

        Best Regards,
        Community Support Team _ kalyj

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.