Measure based on count of another measure

[dyugov]

Hi,

Editing this message to rpovide a better sample data and expected outcome, per suggestion.

Relatively new to Power BI here. I am building a report of sales orders that are ready to ship based on available inventory. I have a table 'OrderLines'(below) with each sales order line for all sales orders that have not been shipped. Some of the lines are not the items themselves but what we call configured kits(think of them as collection of other items). They are not in inventory themselves but can only ship if all the items in that collection can ship. They are designated "CONFIG" in "isConfig" field and always have a whole number as "SO_LINE_NO"(i.e 3), with all the items in that kit having same line number with decimals(i.e. 3.01)

I have built a measure "canShip" that checks the inventory based on region\quantity\expiration date\etc and determines whether a certain line can ship. I understand that it's not in the table but I've added it here to show the expected outcome.

What I am trying to figure out is another measure that would let me figure out if I can ship the kit, if all the items in that kit are ready to ship. Also, parentLine is a calculated column(basically rounddown of the SO_LINE_NO) if that makes any difference.

SO_ID		SO_LINE_NO		PART_ID		CONFIG_ID		isConfig		parentLine		canShip
289439		1		316111						1		Y
289439		2		300041						2		Y
289439		3		322430				CONFIG		3
289439		3.01		322000		322430				3		Y
289439		3.02		322330		322430				3		Y
289439		3.03		322500		322430				3		Y
289439		3.04		310091		322430				3		Y
289439		4		588621						4		Y
289439		5		405911-C2						5		N
289439		6		323100				CONFIG		6
289439		6.01		322000		323100				6		Y
289439		6.02		322381		323100				6		Y
289439		6.03		323110		323100				6		N
289439		6.04		310091		323100				6		N
289439		6.05		322809		323100				6		Y
289440		3		322300				CONFIG		3
289440		3.01		322000		322300				3		Y
289440		3.02		322330		322300				3		N
289440		3.03		322310		322300				3		Y
289440		3.04		310091		322300				3		Y
289440		4		316991						4		Y

In that example, SO_LINE_NO 3 of SO_ID 289439 should be ready to ship("Y"), as all the "sub-line" items have "Y" in canShip measure. SO_LINE_NO 6 of the same order and SO_LINE_NO 3 of a different order would have "N" in that measure, because not all items for those kits are ready yet.

Basically, I am trying to check whether all items that have the same "SO_ID" and "parentLine" as the "SO_LINE_NO" of the configured kit, have "Y" in canShip measure.

Any help with that is greatly appreciated.

[Anonymous · Accepted solution]

Hi @dyugov ,

Please check the formula:

Measure = 
var check = MINX(FILTER(ALLEXCEPT('Table','Table'[SO_ID],'Table'[parentLine]),[canship]<>BLANK()),[canship])
return
IF([canship]=BLANK(),check,[canship])

Best Regards,

Jay

[Anonymous]

Hi @dyugov ,

Please share some sample data and expected result.

How to Get Your Question Answered Quickly - Microsoft Power BI Community.

Best Regards,

Jay

[dyugov]

Hi @Anonymous , 

Thanks you for reaching out, I've edited the OP. Hopefully, this is more descriptive.