Stock in exactly selected Warehouses (with PBIX)

[rks]

Hi Community,

I am struggling with a difficult DAX-problem (I believe). I am wondering whether some of you has already solved the general problem. I have prepared a very easy PowerBI file (https://filetransfer.io/data-package/iAmADv6N#link).

The scenario and the raw data:

We want to analyse, what quantities of stock a available at our warehouses. That's fairly easy:

We have a total stock of 4979 items and have 4 different SKU.

However, the tricky part is: I want to check exactly what combinations of warehouses are available and see the exact amount of stock at this warehouses (manually prepared screenshot in Excel): 

1) I think we need a table with all possible combinations of A,B,C. Could this be made automatically? Let's say if I have 4 or 5 warehouses? (in my real model I have created a table with the grain of Group and warehouse and have created a bidirectional filter)

2) I need to check for each group if the items are exactly in this combination and no other.

Additional info: The real scenario really has got 3 warehouses but several hundreds of thousands of SKU and several million items of stock-quantity per day.

I have created a measure and I believe it works. But it takes 30s for a query. Upon request I could provide this measure but it looks quite complicated and I am looking for a much simplier solution.

If someone else has found a solution of this problem I would be more than happy to be pointed into this direction 🙂

Best regards,

Konstantin

[johnt75]

If you don't need to apply any filters to the stock, you could create a summary table like

Summary Table = 
VAR SummaryTable =
SUMMARIZECOLUMNS(
	'Stock'[ItemNo],
	"Warehouses", CONCATENATEX( VALUES( 'Stock'[Warehouse]), 'Stock'[Warehouse], " / "),
	"@Sum of stock", [Sum of Stock]
)

RETURN
GROUPBY(
	SummaryTable, [Warehouses],
	"Stock", SUMX(CURRENTGROUP(), [@Sum of stock])
)

[rks]

Hi @johnt75 , that looks very promising and produces the correct results, however in reality, the fact table is much larger and filters will be applied. But I assume I could replace SUMMARIZECOLUMNS with ADDCOLUMNS and SUMMARZIE?

[johnt75]

Yes you could, but if you're going to be using it in a measure I don't know how good performance will be and you will need to pay special attention to the filter context. As a calculated table there is no filter context, so everything is summarized, but if you place a measure in a visual then you need to consider if any of the filters applied, internal to the visual or externally from slicers etc, need to be removed.

[rks]

My model is connected with a live-connection to a dataset. In PowerBI I cannot output a table AFAIK. But this solution gives me something to ponder. I'll think about why it works.

[johnt75]

a composite model could be an option. that lets you keep the live connection to the dataset but also add local tables and measures. worth thinking about

[rks]

Hi @johnt75 , composite models are not yet feasible. There are too many caveats that outweigh the advantages especially when using big models with hundreds of measures (e.g. the format string gets lost).

But I stil wonder, why your solution works. I probably don't really understand the internals of the summarizecolumns function. 

Eventually I would like to produce a measure and group over all exisiting warehouse combinations that are stored in a single table. I have uploaded another file:
https://filetransfer.io/data-package/UU7xCeaO#link

I am still eloborating on this. But I wonder whether you could elaborate on why the summarizecolumns works?

[johnt75]

The SUMMARIZECOLUMNS in the code I posted is really little more than a souped up version of ADDCOLUMNS ... VALUES. Because it is only grouping by one column, the Item number, it could easily have been replaced with values. It just gets all the unique values from the item number column, but then the other arguments are all calculated with the item number in the filter context, unlike in ADDCOLUMNS where you would need to wrap them inside a CALCULATE to perform context transition. 

In the 2nd part of the query I had to use GROUPBY rather than SUMMARIZE because you can't use SUMMARIZE or SUMMARIZECOLUMNS on a variable, you have to use a base table.

[rks · Accepted solution]

@johnt75 , thank you for your guidance. I was able to produce a measure on my small dataset:

Measure 2 = 
var _Filter = MAX(Groups[Group])
RETURN
SUMX(
 	FILTER(
 		ADDCOLUMNS(
			VALUES('Stock'[ItemNo]),
			"@Warehouses", 
			CALCULATE(
				CONCATENATEX(
					VALUES('Stock'[Warehouse]), 'Stock'[Warehouse], "/", 'Stock'[Warehouse], ASC
				),
			ALL(Groups)
			),
			"@StockQty", [Sum of Stock]
		),
	[@Warehouses] = _Filter
 	),
	[@StockQty]
)

For that to work the concatenation need to produce the same values (in the right order) that are saved in the Groups table. 

[mh2587]

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dax-Product-combinations/m-p/1892855 
referring to this article