Forum Discussion
Why doesn't a column calculation work properly using a "What If " Parameter
Hi there,
This is my first time at posting a question for many months because 95% of the time I can find my answer within existing Forum discussions.
I suspect I am doing something stupid but here goes ...
WHY DOESN'T THE FOLLOWING COLUMN CALCULATION WORK ?
- Discount Price COLUMN = Table1[Price] * (1-Discount[Discount Value])
PBI doesnt appear to be aware of the current SLICER value of Discount[Discount Value] and uses the DEFAULT value instead.
When I define the DISCOUNT as a MEASURE then it works. But I dont want to have to do this .
- Discount Price MEASURE = SUMX(Table1, Table1[Price] * (1-Discount[Discount Value]))
Screeshot below.
Thanks,
Nick Aubrey
Hi, calculated columns are only calculated when the data model is initialized (at load or refresh) which is BEFORE you interact with the model in any way (slicers, crossfiltering etc). So calculated columns will ALWAYS use default values for any slicers or disconnected tables.
Its the way PowerBI works. What is the reason your trying to add a calulated column vs displaying the measure in a visual?
Also I woudl AVOID using SUMX if at all possible. Its very innefecient, slow and resource intensive. Try
Discount Price MEASURE = SUM(Table1[Price]) * (1-MAX(Discount[Discount Value]))
Let PowerBI do the filter context for you. Finally assuming your discount table is a disconnected table that is NOT connected to your data I would reccommend a harvest measure to get the selected value and set a default if none (or more than one) discount is selected. In your case
Selected Discount = SELECTEDVALUE(Discount[Discount Value],0) // will default to 0 or no discount repalce with .1 or whatver you want as your default)
with this harvest masure your Discount Price Measure woudl be
Discount Price MEASURE = SUM(Table1[Price]) * [Selected Discount]
2 Replies
Seward12533Solution Sage
Hi, calculated columns are only calculated when the data model is initialized (at load or refresh) which is BEFORE you interact with the model in any way (slicers, crossfiltering etc). So calculated columns will ALWAYS use default values for any slicers or disconnected tables.
Its the way PowerBI works. What is the reason your trying to add a calulated column vs displaying the measure in a visual?
Also I woudl AVOID using SUMX if at all possible. Its very innefecient, slow and resource intensive. Try
Discount Price MEASURE = SUM(Table1[Price]) * (1-MAX(Discount[Discount Value]))
Let PowerBI do the filter context for you. Finally assuming your discount table is a disconnected table that is NOT connected to your data I would reccommend a harvest measure to get the selected value and set a default if none (or more than one) discount is selected. In your case
Selected Discount = SELECTEDVALUE(Discount[Discount Value],0) // will default to 0 or no discount repalce with .1 or whatver you want as your default)
with this harvest masure your Discount Price Measure woudl be
Discount Price MEASURE = SUM(Table1[Price]) * [Selected Discount]
Your statement that column values are determined ... BEFORE you interact with the model in any way ... has resolved my issue. The Power BI example I used was a very simple one but I now feel confident of repeating the same concepts on a much more complicated pbix that I am working on.
Thanks so much :smileyhappy:
