Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear community,
I've already posted this once, but I didn't receive any response after sharing my dataset so I'm trying it again.
I'm currently struggling with a DAX calculation. I've created the following DAX measure:
Total Trans QTY LTM =
VAR _Date = MAX('Date'[Date])
VAR _Startdate = DATE(YEAR(_Date)-1;MONTH(_Date);DAY(_Date))
VAR _BOMitem = IF(SELECTEDVALUE('ITEMID BOM'[BOM version.BOMID])="Blank";1;0)
VAR _Workorder = FIND(SELECTEDVALUE('INVENTTRANS OUT MERGED'[REFERENCEID]);"WO";1;0)
VAR _Table = FILTER('INVENTTRANS OUT MERGED';'INVENTTRANS OUT MERGED'[INVENTTRANS.DATEPHYSICAL]>=_Startdate)
Return
SUMX(_Table;'INVENTTRANS OUT MERGED'[INVENTTRANS.QTY]*-1)
But I want to change the return part. Just for some context, I'm trying to calculate the stock turnover, but for certain items that calculation needs to be different from others. Therefore I already wrote 2 variables to classify those requirements. What I want in the end is:
When the BOM version. BOMID returns 1 I want to keep the same result as now presented, so INVENTTRANS.QTY *-1 on the filtered _Table. But when the BOM version.BOMID returns 0 (so not blank) I want the INVENTTRANS.QTY to be filtered, because I don't want to include the qty that has "WO" in the REFERENCEID. Therefore I already put in the requirement for identifying "WO", but after that I'm lost. I've tried to do it with an IF statement but because I'm trying to combine 2 calculations into 1, I don't have a necessary True or false outcome. It just needs to be one or the other.
First the item table in which the BOMid is registered.
ITEMID | BOMID |
dog | "Blank" |
cat | B001 |
Then, the table in which the quantity is registered per referenceid.
ITEMID | REFERENCEID | QTY |
dog | WO1 | 5 |
dog | WO2 | 5 |
dog | IC01 | 5 |
dog | IC02 | 5 |
dog | SO1 | 5 |
dog | SO2 | 5 |
cat | WO3 | 5 |
cat | WO4 | 5 |
cat | IC03 | 5 |
cat | IC04 | 5 |
cat | SO3 | 5 |
cat | SO4 | 5 |
And finally, the numbers PowerBI should come up with.
ITEMID | QTY |
dog | 30 |
cat | 20 |
So just to summarize, if the bomid isn't empty I want to include all the quantities, except the ones that contain "WO" in the REFERENCEID. If the bomid is empty, I want to include all the quantities, so also "WO". I didn't elaborate on the maxdate function, because that's already doing what it should do.
I really appreciate the help!
Gr. Diana
Solved! Go to Solution.
@DLU,
This solution uses the data model below:
Measures:
Sum QTY = SUM ( 'INVENTTRANS OUT MERGED'[QTY] )
Total Trans QTY LTM =
VAR vTable =
ADDCOLUMNS (
'ITEMID BOM',
"@Qty",
VAR vBOMID = 'ITEMID BOM'[BOMID]
RETURN
IF (
vBOMID = "Blank",
[Sum QTY],
CALCULATE (
[Sum QTY],
NOT CONTAINSSTRING ( 'INVENTTRANS OUT MERGED'[REFERENCEID], "WO" )
)
)
)
VAR vResult =
SUMX ( vTable, [@Qty] )
RETURN
vResult
Add 'ITEMID BOM'[ITEMID] and the second measure to a visual:
Proud to be a Super User!
@DLU,
This solution uses the data model below:
Measures:
Sum QTY = SUM ( 'INVENTTRANS OUT MERGED'[QTY] )
Total Trans QTY LTM =
VAR vTable =
ADDCOLUMNS (
'ITEMID BOM',
"@Qty",
VAR vBOMID = 'ITEMID BOM'[BOMID]
RETURN
IF (
vBOMID = "Blank",
[Sum QTY],
CALCULATE (
[Sum QTY],
NOT CONTAINSSTRING ( 'INVENTTRANS OUT MERGED'[REFERENCEID], "WO" )
)
)
)
VAR vResult =
SUMX ( vTable, [@Qty] )
RETURN
vResult
Add 'ITEMID BOM'[ITEMID] and the second measure to a visual:
Proud to be a Super User!
Thank you so much for your help DataInsights, I was really stuck on this for a few weeks. I hope you don't mind me asking, but I always try to figure out how the DAX works to I can use it for future purposes also. Could you explain what the "@qty" function does? I haven't seen it before in other DAX measures...How does PowerBI know it needs to use the qty column that's actually called different? And am I interpreting it correctly that the VAR vBOMID is actually not a seperate VAR but it's inside the VAR vTable?
@DLU,
Glad to hear that works. The "@Qty" is actually a temporary column; you can name it anything you want. The variable vBOMID is a nested variable within the variable vTable. Since ADDCOLUMNS is an iterator, vBOMID enables us to get the BOMID for each iteration, and calculate the measure [Sum QTY] in the appropriate context. Once the table has been iterated, vTable contains all the necessary data, and then SUMX iterates vTable.
Hope this helps!
Proud to be a Super User!
Thank you so much for your willingness to explain it to me, it makes perfect sense now and I'm sure this will help solve a lot of my problems I ran into!
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |