how to display a record based on value of other record in same table

[tvanover]

Hello,

I have a database which is very simple: 1 table, with 4 fields:

Item,  Stock, Location and LocationType

Like this:

25021, 17, 3B333, BULK (17 pieces of item 25021 are stored in location 3B333, which is a bulk location)

25021,6,4B516, PICKING

10012,4,4B336,PICKING

etcetera...

the setting is a warehouse were products are stored in specific locations. We keep the stock per location.

A location can be a picking box, or a bulk location.

When the picking box is empty, we need to move stock from the bulk location to a picking location.

I need to work out a strategy to detect which items need to be moved from a bulk to a picking location.

My thinking is this:

- if there are 1 or more records for an item and one of these locations is of type BULK, then I want to see all records for that item.

- if there is only 1 record and the locationtype is PICKING, I do not want to see that record

But I cannot figure out how the value of a field in a record (in the same table) can be used when evaluating another record.

I am sure it can be done. Can anyone point me in the right direction?

regards,

Tom

[Anonymous]

Hi all,thanks for the quick reply, I'll add more.

Hi @tvanover ,

Use the following DAX expression to create a measure

Measure = 
VAR _needMovement = CALCULATE(CONCATENATEX('Table',[LocatieType]),ALLEXCEPT('Table','Table'[ItemCode],'Table'[ItemDescription]))
RETURN
IF(CONTAINSSTRING(_needMovement,"BULKHOOG"),"Need Movenment")

Final output

If I understand wrongly, please correct me.

Best Regards,
Wenbin Zhou

[Kedar_Pande]

@tvanover 

Add a new calculated column to identify items that need action:

Needs Movement = 
VAR ItemHasBulk = 
CALCULATE(
COUNTROWS('Table'), 
'Table'[Item] = EARLIER('Table'[Item]) && 'Table'[LocationType] = "BULK"
)
RETURN
IF(
ItemHasBulk > 0, 
1, 
0
)

Use a table visual and filter rows where Needs Movement = 1.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[tvanover]

Hi,

Thank you. I tried it but it's not exactly the result that I need. But perhaps I didn't explain well enough.

Here's an example for 2 items, based on your code:

You can see it only identified one of the 2 "25021" records. It should identify both 25021 records, because one of it contains the 'BULKHOOG' locationtype. The 2 "10064" should not be identified, because the locationtypes are not 'BULKHOOG'.

ItemCode	ItemDescription	Stock	Locatie	LocatieType	Needs Movement
10064	Comed Stopmite duif 300 gram*	16	3B836	PICKING	0
10064	Comed Stopmite duif 300 gram*	3	3B732	PICKING	0
25021	Pigo Appelazijn Classic 2 liter	17	3B333	BULKHOOG	1
25021	Pigo Appelazijn Classic 2 liter	6	4B516	PICKING	0

This is the current code:

Needs Movement = VAR ItemHasBulk =

CALCULATE(

COUNTROWS('StockPerLocatie'),

'StockPerLocatie'[ItemCode] = EARLIER('StockPerLocatie'[ItemCode]) && 'StockPerLocatie'[LocatieType] = "BULKHOOG"

)

RETURN

IF(

ItemHasBulk > 0,

1,

0

)

This is the desired result:

ItemCode	ItemDescription	Stock	Locatie	LocatieType	Needs Movement
10064	Comed Stopmite duif 300 gram*	16	3B836	PICKING	0
10064	Comed Stopmite duif 300 gram*	3	3B732	PICKING	0
25021	Pigo Appelazijn Classic 2 liter	17	3B333	BULKHOOG	1
25021	Pigo Appelazijn Classic 2 liter	6	4B516	PICKING	1

I'd appreciate if you could have another look at it.

[Sahir_Maharaj]

Hello @tvanover,

Can you please try this approach:

1. First, check whether a BULK location exists for each Item

HasBulkLocation = 
IF(
    CALCULATE(
        COUNTROWS(YourTable),
        YourTable[Item] = EARLIER(YourTable[Item]),
        YourTable[LocationType] = "BULK"
    ) > 0,
    TRUE,
    FALSE
)

2. Then, create a measure to determine which records should be displayed

ShowRecord = 
IF(
    [HasBulkLocation] = TRUE 
    || YourTable[LocationType] = "BULK",
    1,
    0
)

[tvanover]

Thanks, your approach is similar to the first poster's solution, but as a consequence has the same problem (as I outline above)