Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
rks
Resolver II
Resolver II

Stock in exactly selected Warehouses (with PBIX)

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:

rks_2-1665150322921.png

 

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

 

rks_1-1665150248952.png

 

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): 

rks_0-1665150236001.png

 

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

1 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. 

View solution in original post

9 REPLIES 9
johnt75
Super User
Super User

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])
)

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?

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.

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.

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

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?

 

 

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.

@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
Super User
Super User

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


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.