Forum Discussion
Changing Calculated Columns to Measures
Hello All,
I am new to Power BI and DAX. Last year Power BI got introduced at my company but we where not happy with the result. After many trial and error runs, i managed to get everything shown in a way that is helpfull for us. This Forum helped me out on many occasions.
A few weeks back however i noticed there where some values (calculations) not showing what they needed to show. Seems there is a major difference between Calculated columns and Measures ☺. Since i did not know about those, i simply 'used what worked' for me, mainly Calculated columns as the formulas i used are easy ones. Now it seems some of them should have been Measures. There are quite some formulas (Calculated Columns) using other formulas (Calculated Columns).
In this file, the main fomula i think would be: POStuklijst[Vold. Besteld? JA/NEE] this formula uses most other formulas.
2 formulas that have already been changed into measures are:
POStuklijst[Besteld aantal] --> POStuklijst[Besteld AantalMeasure]
POStuklijst[Vold. Besteld?] --> POStuklijst[Vold. BesteldMeasure]
Please note i am hoping to fix just the first tab (named 'Productieorders'). The others are not used as much.
Would anyone be willing to help me get the 'wrong' Calculated columns changed into Measures? Hope to hear from you soon! Thanks a lot in advance.
Hello JimmyBos ,
Thank you for reaching you to Microsoft Fabric Community Forum.
To keep it simple:
- Use calculated columns when you need a value for each row, like a lookup result or a text check.
- Use measures when you're doing calculations across rows, like totals, differences, or anything that should react to filters or slicers.
In your case, changing [Besteld aantal] and [Vold. Besteld?] to measures was the right move. But since your main formula [Vold. Besteld? JA/NEE] is still a calculated column, it can’t use those measures directly — that’s why it broke.
You have two options:
-
Convert [Vold. Besteld? JA/NEE] to a measure too, which would be the cleaner solution in the long run.
- Or if that's too much for now, temporarily go back to using the original calculated columns in that formula to keep things working.
Lastly, you are right: things like stock, price, and other fixed values should be columns; calculations like totals and comparisons should be measures.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
9 Replies
If you could share the formulas used to create the calculated columns as a post here. I'd see if I can take a look. It would also be helpful to get a screenshot of the data model as this impacts how measures could be created.
Hello SEMattis , Thank you for the fast reply. The data model:
So i am still not sure which formulas (Calculated Columns) are fine and which need to be changed to Measure.
The 'main' formula would be:
POstuklijst[Vold. Besteld? JA/NEE] = IF(POStuklijst[JA/NEE Beschikbare voorraad] = "NEE" ,IF(POStuklijst[Vold. Besteld?]>=0,"JA",IF(POStuklijst[Voorraad Besteld]>=POStuklijst[gbkmut.aantal],"JA",IF(POStuklijst[Productie Order VR]+POStuklijst[StockQTY_Correct]>=POStuklijst[gbkmut.aantal],"JA",IF(POStuklijst[Vold. Besteld?]+POStuklijst[StockQTY_Correct]>=0,"JA?",IF(POStuklijst[Bouten&Moeren]=TRUE(),"JA","NEE"))))) , BLANK() )Second 'Layer' formulas:POstuklijst[JA/NEE Beschikbare voorraad] = IF( POStuklijst[Uitgegeven] = "JA" , BLANK() , IF((POStuklijst[StockQTY-TotaalBehoefte])>-1,"JA","NEE"))POstuklijst[Vold. Besteld?] = IF(POStuklijst[JA/NEE Beschikbare voorraad] = BLANK() , BLANK() , POStuklijst[Besteld aantal] - POStuklijst[gbkmut.aantal])POstuklijst[Voorraad Besteld] = IF( POStuklijst[VR aantal besteld]>0 , POStuklijst[VR aantal besteld] , BLANK())POstuklijst[Productie Order VR] = Calculate(SUM(ProductieOrders[Quantity]),FILTER(ALL(ProductieOrders),ProductieOrders[Item]=POStuklijst[artcode]) )POstuklijst[Bouten&Moeren] = CONTAINSSTRING(POStuklijst[description],"Tapbout")|| CONTAINSSTRING(POStuklijst[description],"Moer, zeskant")|| CONTAINSSTRING(POStuklijst[description],"Moer, zelfborgend")|| CONTAINSSTRING(POStuklijst[description],"Carosseriering")|| CONTAINSSTRING(POStuklijst[description],"Stafstaal")|| CONTAINSSTRING(POStuklijst[description],"Sluitring")|| CONTAINSSTRING(POStuklijst[description],"Onderlegring")|| CONTAINSSTRING(POStuklijst[description],"Draadstang")|| CONTAINSSTRING(POStuklijst[description],"Lasstiftbout")|| CONTAINSSTRING(POStuklijst[description],"Assemblage")|| CONTAINSSTRING(POStuklijst[description],"Dummy")|| CONTAINSSTRING(POStuklijst[description],"Slotbout")|| CONTAINSSTRING(POStuklijst[description],"Stelschroef")Third 'Layer' formulas:POstuklijst[VR aantal besteld] = CALCULATE(SUM(Bestellingen[Besteld Aantal]) , Userelationship(POStuklijst[artcode] , Bestellingen[LinkProjectArtcode] ))POstuklijst[Uitgegeven] = IF(POStuklijst[Checked] - POStuklijst[Completed] = 0 , "JA" , "NEE" )POstuklijst[StockQTY-TotaalBehoefte] = POStuklijst[StockQTY] - POStuklijst[Stuklijst Totaal per artikel]POstuklijst[Besteld aantal] =CALCULATE(SUM(Bestellingen[Besteld Aantal]),USERELATIONSHIP(Bestellingen[LinkProjectArtcode],POStuklijst[LinkParentArtcode]))Fourth 'Layer' formulas:Bestellingen[Besteld Aantal] = Bestellingen[esr_aantal] * Bestellingen[unitfactor]Bestellingen[LinkProjectArtcode] = CONCATENATE( Bestellingen[Project clean],Bestellingen[artcode])POstuklijst[StockQTY] = LOOKUPVALUE(InkoopArtikelenStockqty[StockQty],InkoopArtikelenStockqty[ItemCode], POStuklijst[artcode])POstuklijst[Stuklijst Totaal per artikel] = SUMX(FILTER('POStuklijst','POStuklijst'[artcode] = EARLIER('POStuklijst'[artcode])&& POStuklijst[Checked] = 1&& POStuklijst[Completed] = 0),'POStuklijst'[gbkmut.aantal])Hope this helps! Let me know if you need anything else.So if you are unsure whether to use calculated columns vs measures I would recommend you to read the following article which gives you a good understanding of the difference between calculated columns and measures (Calculated Columns and Measures in DAX - SQLBI).
To summarize:
Calculated Columns:
- Definition: Computed during data refresh, these columns are added to tables using DAX expressions evaluated row by row.
- Evaluation Context: Operate within the row context, meaning each row's calculation is independent of others.
- Storage: Values are stored in memory, which can impact RAM usage.
- Use Cases:
- Creating slicers or placing results in rows/columns of pivot tables.
- Defining expressions strictly bound to the current row, such as calculating line totals (Price * Quantity).
- Categorizing data, like segmenting customers by age ranges.
Measures:
- Definition: Dynamic calculations evaluated at query time, often aggregating data based on user interactions.
- Evaluation Context: Depend on the filter context, which is determined by report selections, slicers, or axes in charts.
- Storage: Not stored in memory; computed on-the-fly during queries.
- Use Cases:
- Performing calculations that aggregate or summarize data, such as sums, averages, or ratios.
- Creating dynamic metrics that respond to user interactions in reports.
Choosing Between Calculated Columns and Measures:
- Use calculated columns when you need to filter or categorize data at the row level, especially if the result will be used in slicers, rows, or columns of a report.
- Use measures for calculations that aggregate data and need to respond dynamically to user interactions and filters in reports.
Based on the DAX syntax provided I could suggest some improvements (such as not using nested IF statements) to improve the overall performance of the report. What is important is that you decide whether you see a need to convert your calculated columns to measures.
First i would like to point out that i changed the formulas in my previous post. I forgot the tabel.
I have read the article you posted before. Even with your explenation i am affraid i just don't understand when to chose which. I understand what you are saying but i have no idea on how to use this information to make the choise 'Calculated Column or Measure'.
The problem occured with the formula POStuklijst[Besteld aantal] & POStuklijst[Vold. Besteld?], thats why i changed these to measures.
POStuklijst[Besteld AantalMeasure] = CALCULATE(
SUM(Bestellingen[Besteld Aantal]),USERELATIONSHIP(Bestellingen[LinkProjectArtcode], POStuklijst[LinkParentArtcode]))POStuklijst[Vold BesteldMeasure] =
VAR Besteld = [Besteld AantalMeasure]VAR Geleverd = SUM(POStuklijst[gbkmut.aantal])VAR Resultaat = Besteld - GeleverdRETURN IF(Resultaat = 0, BLANK(), Resultaat)But, after changing these 2 formulas to measures i was not able to use them in the formulas in my previous post. I was hoping you or someone else is able to see which formula's also need to change into measures. Or would it be an option to JUST change the main formula? So that it contains some Calculated Column formulas and Measures.
Hello JimmyBos ,
Thank you for reaching you to Microsoft Fabric Community Forum.
To keep it simple:
- Use calculated columns when you need a value for each row, like a lookup result or a text check.
- Use measures when you're doing calculations across rows, like totals, differences, or anything that should react to filters or slicers.
In your case, changing [Besteld aantal] and [Vold. Besteld?] to measures was the right move. But since your main formula [Vold. Besteld? JA/NEE] is still a calculated column, it can’t use those measures directly — that’s why it broke.
You have two options:
-
Convert [Vold. Besteld? JA/NEE] to a measure too, which would be the cleaner solution in the long run.
- Or if that's too much for now, temporarily go back to using the original calculated columns in that formula to keep things working.
Lastly, you are right: things like stock, price, and other fixed values should be columns; calculations like totals and comparisons should be measures.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi JimmyBos ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
B Manikanteswara Reddy
