2 Columns with same value should indicate if they have all a specific value in a 3rd column

[Torsten82]

Hi there,

in our commissioning area we want to visualize which orders can be done right away from the stock.

Therefore I need a new column that indicates "ready for commissioning" 

if all lines 'in stock' are green for  the same value in 'order' and 'delivery date'.

So I can later set a filter to show only orders whichs are in in stock somplete.

Any suggestions?

in Stock		order		delivery date		article		value
green		4249026		20.10.2023		1940213		800
green		4249265		11.08.2023		4200524		100
green		4249265		11.08.2023		4300706		40
red		4249270		23.10.2023		6212522		13
red		4249270		23.10.2023		6212522		1300
green		4249270		23.10.2023		3041122		4
green		4249270		23.10.2023		3044203		3
green		4249475		31.08.2023		3630299		12
green		4249475		31.08.2023		3630299		12
green		4249632		16.08.2023		3040224		9
green		4249632		16.08.2023		2600264		8
green		4249632		16.08.2023		2656655		20
yellow		4249665		11.10.2023		5370035		60
green		4249715		05.10.2023		3690316		62
red		4249715		05.10.2023		6711136		26

[Anonymous · Accepted solution]

Hi @Torsten82 ,

You can create a calculated column as below to get it:

ready for commissioning = 
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[order] ),
        FILTER (
            'Table',
            'Table'[order] = EARLIER ( 'Table'[order] )
                && 'Table'[delivery date] = EARLIER ( 'Table'[delivery date] )
        )
    )
VAR _countgreen =
    CALCULATE (
        COUNT ( 'Table'[order] ),
        FILTER (
            'Table',
            'Table'[order] = EARLIER ( 'Table'[order] )
                && 'Table'[delivery date] = EARLIER ( 'Table'[delivery date] )
                && 'Table'[in Stock] = "green"
        )
    )
RETURN
    IF ( _count = _countgreen, "Yes", "No" )

Best Regards

[AilleryO]

Hi,

Not sure it's the most efficient solution, but it works :

TestOrder =
VAR CurrOrder = SELECTEDVALUE( TableOrder[order ] )
VAR CountOrder = COUNTROWS( FILTER( ALL(TableOrder) , TableOrder[order ]=CurrOrder ) )
VAR CountGreen = COUNTROWS( FILTER( ALL(TableOrder) , TableOrder[order ]=CurrOrder && TableOrder[in Stock]="green" ) )
RETURN
IF( CountOrder-CountGreen <> 0, "Not OK", "OK" )

like this :

Hope it helps

[Torsten82]

A good way, however I wonder, becuase it shows me always "OK"...?

Beside that I am missing to include the delivery date, because one order could have multiple different delivery dates.