CODEWITHSUNDEEP
powerbidaxcalculated-columnsmeasure
Carlos Tellez
Carlos Tellez

Reputation: 157

How to use Measures in Calculated column Formula in PBI

I'm trying to use a previous calculated Measure in a Calculated Column in PBI, but i get the error "expressions that yield variant data-type cannot be used to define calculated columns", how can i fix this? this is my code

Critical_Detrended = 1*(1+[Trend])

Thanks

Upvotes: 0

Views: 1342

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40234

While calculated columns are not responsive to any slicers or filters set on your report, you absolutely can use measures in a calculated column definition so long as you understand how the row context to filter context transition works and don't expect the calculated column to be dynamically responsive.

That said, the error you are getting sounds like [Trend] may output different data types in different situations but a column can only be a single data type. A non-obvious case where this sort of thing happens is when a measure sometimes outputs an integer and other times outputs a decimal type. In a case like that, you can force it to return a decimal type by multiplying by 1.0 or adding 0.0 or similar manner of type coercion. In your case, changing one of the 1 to 1.0 might do the trick if that's indeed the problem.

Upvotes: 0

Andrey Nikolov
Andrey Nikolov

Reputation: 13460

Calculated columns are static. They are calculated once, when the data is loaded. Their values cannot change. Thus, you can't use something dynamic, like measures, because measures are responsive, they are re-computed when the user selects something, changes the value of a slicer, etc.

The solution in your case is to change your calculated column to be a measure too.

You could take a look for example at Calculated columns and measures in DAX article.

Upvotes: 0

Related Questions