Turn a calculated column into a measure

[datadonuts]

I am working on a very simple datamodel to transfer a calculated column from a table into a measure.

It was pretty easy to create a calculated column in my fact table, but actually I want to avoid that and only work with DAX measures.

The tables are fSales and disDiscount. No relationship between them.

My calculated column function is:

Discounted product price

=ROUND(

LOOKUPVALUE(

dProduct[Price],[Product],fSales[Product])*

(1-CALCULATE(

MAX(disDiscount[Discount]),

FILTER(

disDiscount, fSales[Quanity]>=disDiscount[Units]))),2)

The result using a calculated columns looks like this. How to get the same result only using DAX measures?

(the related product price is just from a related product table USING [Product])

It works as calculated column  but not as measure??? 😫

Files (from excel)

[datadonuts]

Thanks for all your support. I figured out a solution. Is that a reasonable one? What other solutions are possible/easier/better?

[amitchandak]

@datadonuts , as there are one many relations, you should be able to use

related(dProduct[Price])

sumx(sales, sales[qty]*related(dProduct[Price]) )

[lbendlin]

Don't forget what calculated columns are for, and what measures are for

calculated columns:  when your computation is only dependent on the source data but not on any user filter choices.

Calculated columns are computed only once during query refresh. After that they are static and part of the expanded tables.

They are computed in the true row context of your query data. 

Measures: are computed dynamically whenever the user filter choices change. Every single time.  They operate on the filter context of the expanded tables, but are not part of them.

That means a DAX function that worked for a calculated column will most likely not work in a measure, at least not with the same result.  

First you need to decide if you truly need a measure. If so then you need to rewrite your DAX specifically for the goals of that measure (including the tricky parts for totals etc). Use variables to prevent the filter context transitions to mess with your data (and for better legibility)

[az38]

@datadonuts 

smth like this

Discounted product price = 
VAR CurrentProduct = MAX(fSales[Product])

RETURN

ROUND(
CALCULATE(MAX(dProduct[Price]),[Product] = CurrentProduct )
*
(1-CALCULATE(
MAX(disDiscount[Discount]),
FILTER(
disDiscount, fSales[Quanity]>=disDiscount[Units]))),2)