Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Thanks for all your support. I figured out a solution. Is that a reasonable one? What other solutions are possible/easier/better?
@datadonuts , as there are one many relations, you should be able to use
related(dProduct[Price])
sumx(sales, sales[qty]*related(dProduct[Price]) )
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)
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |