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.
Hello Everyone,
Complete Noob to dax here, I am very confused and have been on the verge of quitting trying dax because I just cant wrap my head around how it works at the moment. Having said that, I refuse to throw in a towel and here I am. I am tyring to help someone build a powerbi dashboard and we are trying to model our data to where some basic calculations that are presently being done on an excel sheet can be replicated for our Powerbi model.
Please consider to the snip below :
MOS@3M = This is months of supply at 3 Months, this a measure; the dax for which is as follows :
all i want powerbi to do is just do simple math, i.e = 5 minus 4.88 and the resulting value populated should show up in the last column (this is a column not a measure)
Months supply less leadtime @ 3M.
the dax im currently using is to calculate is :
@eugeneniemand @daxer-almighty @AntrikshSharma
Hi, eugene consider anothe problem I am having ill share a table, and some snaps with you consider the table below
The eventual goal of the table is to assess is to identify the items that can go out of stock and recommend a purchase quantity on 3 month, 6 month and 12 month windows.
Using a 3 month window to test the,
In order to do so, we need to calculate
-3 Month average consumption (consumption_3m measure and working fine)
-Average leadtime(Average lead time in the table image shared above), in our case this is a simple pull from dim table no formula needed
- Months of Supply at 3 Months : (MOS_3m) expression =
MOS_3M = divide([qty stock incoming],[Consumption_3M_MonthlyAverage],999)
- Planned_MOS (planned mos of supply) this is the first challenge, the ouput I am getting that you see in the snap is a sum of sort, the expression is as follows (
Planned_MOS =
DIVIDE(SUM('Dimension Purchasing Inputs'[Turn Rate Target]),12,0)+sum('Dimension Purchasing Inputs'[Safety Stock Months ]) = the desired ouput should be 1.25+1 , 2.25 instead i am getting 43.75 dont know the issue with the dax expression but in the column i just shared the image of , it works as expected but not in a measure, row context issue but for now I dont know how to fix it. thats help number 1 i need,
-MOS_Less_Leadtime_3M_Measure this is the column we just fixed to ensure, the math is executed correctly, this is simply (average lead time - mos_3m) i.e. 7.8 minus 5= 2.8
- baseline_qty_3M = this is our proposed quantity of item based on the months of supply adjusted for lead time, i.e [Planned_MOS ]+ [safety Stock months] times [Consumption_3M_MonthlyAverage] i.e 2*2.25 = 4.5
the expression for baseline_qt_3M is as follows :
sumx('Dimension Item',
IF('Dimension Item'[Buy_Review_Status]= "Yes - Buy Review",
IF(SUMX('Dimension Item','Dimension Item'[MOS_Less_LeadTime_3M]*1)<=0,
(sumx('Dimension Purchasing Inputs',[Planned_MOS]+'Dimension Purchasing Inputs' [Safety Stock Months ])*([Consumption_3M_MonthlyAverage])),
sumx('Dimension Purchasing Inputs',[Planned_MOS]+'Dimension Purchasing Inputs'[Safety Stock Months ])-(sumx('Dimension Item','Dimension Item'[MOS_Less_LeadTime_3M]))),0)) the output at the moment is coming out to be 0, as opposed to 4.5
The is the book to understand all about DAX (and I mean ALL) : "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo. Now, since CALCULATE does context transition one has to be very careful when one uses this function in row contexts. Calculated columns are best calculated in Power Query or---if you can't for some reason---in DAX but WITHOUT CALCULATE. Because CALCULATE effects context transition it'll be agonizingly slow if you use it in a column in a big fact table (you can get away with small dimensions only). Sometimes to the point where it's not feasible to have it in there at all as the calculation does not return...
@safi Since you are calling a measure in the row context of calculated column you are getting incorrect numbers because of context transition.
Can you share an example PBIX or at least some sample data and what you expect the output to be
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |