hi there,
Could you do a mock up report what exactly the actual results would be?
I’m having a hard time understanding what is warehouseref and warehousecomp.
thanks
Keith
Hi @marius
I got thinking 
Please find attached file and see if that meets your requirements.
PBI_test-1.pbix (41.2 KB)
I hope this helps.
Keith
I marked with red filters and modified the result as should be. Please see also pbix file if needed.
For example: I need a list with just the products from warehouse A where are not In Warehouse B .
In the calculated table “tab_compare” DAX formula I need to relpace “A” and “B” based on selection from WarehouseRef and WarehouseComp . The only extra thing in the table results will be “Qty” columns. If you have other solution , please ignore my logic path.
Thank you!
Marius
well, i guess that my solution, the matrix gives the option for you
I remember something similar was asked in the forum a while ago using two slicers on a fact table but i can’t find it right off.
did you do a search in the forum or on youtube?
sorry
Keith
I do know to use matrix to obtain the result you purpose but in this case I need a list that respond to the following example question: " What are the products and Qty from A warehouse that are not found in the B warehouse?"
if you look at the matrix it show product warehouse a, warehouse b warehouse c and total quality for that product line
for example product t has 5 products in warehouse A nothing in b and c
if you look at product x you have 5 (warehouse a), 7 (warehouse b) and 8 (warehouse 8) for grand total of 20)
If you have worked with pivot tables in excel its the same.
keith
Another way to go and if you want to just find a product in a warehouse. Make a product dimension table.
I think is no need to create a product dimension. All the info is there but I am not able with my DAX knowledge to solve the problem.
The matrix solution is not optimal ( is about thousands of rows in the real model) in my case because a need just the short list with the products in Ref selection. With this, I know the product I must transfer to a specific warehouse that is not in stock there.
sorry i couldn’t help more.
I’ve currently got a few solutions I’m working on in the queue ahead of this one, but I recently worked out a solution to a very similar problem with another member. Please take a look at the following thread and see if this helps get you where you need to go:
If not, let me know and I’ll be glad to tackle this one with you when I’ve completed the other two, or someone else on the forum may be able to help you get this one across the goal line.
- Brian
3 Likes
at least I’m trying
@Keith ,
Thanks, and definitely keep at it. I’ve always been a strong proponent of the fact that the forum is here to benefit both the people asking the question (in terms of getting the solutions they need) AND the people responding (in terms of providing opportunities for us to build our own skills).
I have logged a lot of solutions over time, but also have my fair share of tries that didn’t quite get there that someone else built on for the ultimate solution. Both are valuable, so keep on trying. You’ll connect on some, and the ones you don’t you’ll learn a lot for having given it a shot. At least that’s been my experience…
- Brian
1 Like
Thank you both!
It is the first time I ask for help in this forum and I am impressed by people and the fast response.
I am studying the thread Brian mentioned and I will give feedback after.
@marius ,
Welcome to the forum! I think you’ll find it a wonderful and friendly community, with tons of knowledgeable and talented people eager to help.
- Brian
I did not found a solution because:
-
If I use a measure it cannot return a table… Maybe if the measure will return a value (ex: TRUE or False) and add it to a visual like a table based on Inventory and one column to be the calculated measure.
-
The only thing that works is just a calculated table (tab_compare )where I entered the warehouse manually. This is not enough…
If I use a calculated table ( tab_compare_2) the refresh is not dynamic and selected values does not work.
I attach the file with the comments for measure and calculated tables.
PBI_test-2.pbix (54.3 KB)
Please tell me if you have an idea on how to solve it. Thank you!
@marius ,
Okay, I think I’ve got a good solution for you. You were actually very close, both in concept and execution. The DAX needed to be tweaked a bit to clear the filters on inventory, eliminate the use of SUMMARIZECOLUMNS, which will not work in this case (and frankly is a function I try to avoid – instead using the SUMMARIZE/ADDCOLUMNS combination, or in this case VALUES when only a single column is involved) and eliminate some unnecessary summarization and filtering, but the general concept of your measure was spot on.
You also totally had the right idea in option #1 above. What I did was from the difference table, calculated an IF statement that returned a 1 if the selected artID was in the difference table and a 0 otherwise. I then used that binary result to filter the table visual, making it fully dynamic.
Here’s the measure that does the heavy lifting:
And here it is all put together with the measuring the filter pane:
Note: if you want to be able to select multiple warehouses as ref or comp, you can alter your harvest measure to use VALUES() instead of SELECTVALUE.
I go through this subsetting approach in more detail in this video:
I hope this is helpful. Full solution file attached below.
– Brian
eDNA Forum - PBI_test-2.pbix Warehouse EXCEPT Solution.pbix (54.2 KB)
4 Likes
I was close but without your contribution, the problem was still unsolved 
.
Thank you Brian for your solution but also for explaining why you chose a function instead of another .
I am impressed…
2 Likes
@marius ,
Thanks for the kind words – I’m glad we got to a good solution together.
I enjoyed working on this with you. Good luck with the rest of the project.
- Brian