Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello All,
I have this kind of problems.
for an Inventory management tool I am creating I have 3 different tables:
1. StockIN: all the entry records, main fields:
2. StockOUT: all the out records
and then the table 'int_mov' that allows to change WH_cateogories, so related fields are:
*****
I created relationships between
then I've created the power pivot table.
I am struggling with to create the calculated measure that basically allows me to calculate the actual net quantity per Wh_Category_lvl1(so far...). the concept is this one:
CURRENT QTY: qty_IN - Qty_OUT - Qty_mov[FROM] + Qty_mov[TO]
so I've created this:
=SUM(StockIN_entry[Quantity_Units_IN])
-
SUM(StockOUT_entry[Quantity_Units_OUT])
-
CALCULATE(SUM(internal_mov_entry[Quantity_Units_mov]),FILTER(internal_mov_entry,internal_mov_entry[FROM WH_Name_Lv1]=CALCULATE(VALUES(StockIN_entry[WH_Name_Lv1_IN]))))
+
CALCULATE(SUM(internal_mov_entry[Quantity_Units_mov]),FILTER(internal_mov_entry,internal_mov_entry[TO WH_Name_Lv1]=CALCULATE(VALUES(StockIN_entry[WH_Name_Lv1_IN]))))
but seems it cannot work properly with the last argument:
CALCULATE(SUM(internal_mov_entry[Quantity_Units_mov]),FILTER(internal_mov_entry,internal_mov_entry[TO WH_Name_Lv1]=CALCULATE(VALUES(StockIN_entry[WH_Name_Lv1_IN]))))
and finally, what if I want to add WH_cat_Lvl2?
thanks!!!
Solved! Go to Solution.
Hi @fedefabio
Yes everything is clear now. I will try to explain the best I can. What you want to do can be achieved completely using DAX code but you will spend hard time trying to achieve that and the result code will be neither efficient nor readable. The reason is your report uses columns from the fact table and these columns cannot filter other tables automatically hence you need to write extra DAX code every step onwards. For example for the 'StockOUT' table you cannot just SUM the quantity column as it won't be filtered by the 'StockIN[Product_name_IN] cannot filter the 'StockOUT' table unless we CROSSFILTER the relationship to "Both". Example:
StockOUT Qty =
CALCULATE (
SUM ( StockOUT_entry[Quantity_Units_OUT] ),
CROSSFILTER ( StockIN_entry[Serial_nr_IN], StockOUT_entry[Serial_nr_OUT], BOTH )
)
But this will not solve the problem because you are slicing by two columns from the Stock-IN table while the relationship utilizes only one column. Here where things get complicated and it even gets more complicated when dealing with the 'int_mov' table considering the two columns "From" and "To". And we're still talking about level 1 only!
That was the bad news. The good news is that there is a simple and efficient solution for that. As I don't have sample file to work with I would advise to do it in two steps. First step is fixing the data model and create proper relationships. 2nd step is writing DAX code which will be simple code.
STEP 1:
Once all relationships are set please share screenshots of the data model then will guide you through the next step.
Have a great day!
@fedefabio
THe TESTNAME column in the visual from which table? Can you also share a sreeshot of your data model? Thak you and have a great day!
@tamerj1 from Stock_IN
sure!
this is StockIN_entry table
this is from Stock_out
and this below from int_mov
thanks to you and have a great day too! 🙂
Sorry @fedefabio for the late reply. I got distracted with some other stuff.
Actually I was asking about the data model (relationships). However from the sample data you've shared I can guess they're all one-one relationships. Yet, I cannot the see the reason behind manually filtering the sum from the 'int_mov' table. You can simply go for
Difference =
SUM ( StockIN_entry[Quantity_Units_IN] )
- SUM ( StockOUT_entry[Quantity_Units_OUT] )
- SUM ( internal_mov_entry[Quantity_Units_mov] )
+ SUM ( internal_mov_entry[Quantity_Units_mov] )
Unless the actual relationships are not as I expected them to be.
Hi @tamerj1 , no worries at all and thanks again for your time!
By the way, the formula you wrote was the first one I've tried but it was not working.
Relationships are many-to-one as below:
I am afraid that the issue stands in the 'int_mov' table where there are 2 different columns with "Wh_lvl1" (FROM and TO)
Cheers!
F
Excellent now all clear. Can you please write the names three columns involved in the relationships?
Hi @tamerj1 , sure!
in the pivot table we have
for calculation values, columns involves are:
and for internal movment table, we have:
with
Hope everything is clear 🙂
cheers!
F
Hi @fedefabio
Yes everything is clear now. I will try to explain the best I can. What you want to do can be achieved completely using DAX code but you will spend hard time trying to achieve that and the result code will be neither efficient nor readable. The reason is your report uses columns from the fact table and these columns cannot filter other tables automatically hence you need to write extra DAX code every step onwards. For example for the 'StockOUT' table you cannot just SUM the quantity column as it won't be filtered by the 'StockIN[Product_name_IN] cannot filter the 'StockOUT' table unless we CROSSFILTER the relationship to "Both". Example:
StockOUT Qty =
CALCULATE (
SUM ( StockOUT_entry[Quantity_Units_OUT] ),
CROSSFILTER ( StockIN_entry[Serial_nr_IN], StockOUT_entry[Serial_nr_OUT], BOTH )
)
But this will not solve the problem because you are slicing by two columns from the Stock-IN table while the relationship utilizes only one column. Here where things get complicated and it even gets more complicated when dealing with the 'int_mov' table considering the two columns "From" and "To". And we're still talking about level 1 only!
That was the bad news. The good news is that there is a simple and efficient solution for that. As I don't have sample file to work with I would advise to do it in two steps. First step is fixing the data model and create proper relationships. 2nd step is writing DAX code which will be simple code.
STEP 1:
Once all relationships are set please share screenshots of the data model then will guide you through the next step.
Have a great day!
Hi @tamerj1
first of all, apologies for my super late reply and really thanks so much for your help.
At the end I solved the issue building a bridge table and from it generate a pivot table.
thanks again! 🙂
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
15 |
User | Count |
---|---|
39 | |
22 | |
18 | |
15 | |
12 |