Assign value to multiple rows based on rules and values from another column

[abrother]

Hi all,

I have a difficult challenge I'm trying to overcome (at least difficult for me) in that I'm trying to create a new column that assigns a value to a set of rows (serial numbers all from a single production lot #) based on evaluating values in another column. Here's the breakdown:

Each lot produces different SKUs depending on certain factors in production so we might have Lot 123ABC with S/Ns 123ABC-001, 002, and 003 being SKU 123456, while S/Ns 004-006 are a different SKU (456789). Each SKU has a value called a "sub-driver" that I have pulled over to my main table using LOOKUPVALUE, but for each Lot I need to assign an overall "Primary Driver" value which is based on looking at the "sub-driver".

For example, if Lot 123ABC has SKUs that contain sub-drivers SD1 and SD2, and the primary driver in this case is SD1 (this is based on rules), I need to assign the value "PD1" to all of the rows in that lot (all of the S/Ns).

Basically what I'm looking for is a way to search the relevant S/Ns associated with the overall Lot # and then assign the Primary Driver value to all of the S/Ns in that lot based on rules when looking at the "sub-driver" column. I've copied a simple version of what I'm talking about as an example below. In the sample the rules should be that for Lot 123ABC if the SD1 sub-driver is present, then the Primary Driver should be PD1, in Lot 234BCD if the SD3 sub-driver is present then the Primary Driver should be PD3.

Let me know if any additional clarification is needed. Thank you all very much in advance.

Lot #	S/N	SKU	Sub-Driver	Primary Driver
123ABC	123ABC-001	123456	SD2	PD1
123ABC	123ABC-002	123456	SD2	PD1
123ABC	123ABC-003	123456	SD2	PD1
123ABC	123ABC-004	456789	SD1	PD1
123ABC	123ABC-005	456789	SD1	PD1
123ABC	123ABC-006	456789	SD1	PD1
123ABC	123ABC-007	789012	SD3	PD1
123ABC	123ABC-008	789012	SD3	PD1
123ABC	123ABC-009	789012	SD3	PD1
123ABC	123ABC-010	234567	SD1	PD1
123ABC	123ABC-011	234567	SD1	PD1
123ABC	123ABC-012	234567	SD1	PD1
234BCD	234BCD-001	789012	SD3	PD3
234BCD	234BCD-002	789012	SD3	PD3
234BCD	234BCD-003	789012	SD3	PD3
234BCD	234BCD-004	123456	SD2	PD3
234BCD	234BCD-005	123456	SD2	PD3
234BCD	234BCD-006	123456	SD2	PD3
234BCD	234BCD-007	345678	SD4	PD3
234BCD	234BCD-008	345678	SD4	PD3
234BCD	234BCD-009	345678	SD4	PD3
234BCD	234BCD-010	345678	SD4	PD3

[Bibiano_Geraldo · Accepted solution]

Hi @abrother ,

Create a new calculated column with the following DAX:

Primary Driver = 
VAR CurrentLot = 'Table'[Lot #]
VAR HasSD1 = CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Lot #]),
    'Table'[Sub-Driver] = "SD1"
)
VAR HasSD3 = CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Lot #]),
    'Table'[Sub-Driver] = "SD3"
)
RETURN 
SWITCH(
    TRUE(),
    HasSD1 > 0, "PD1",
    HasSD3 > 0, "PD3",
    "Unknown" -- Default value if no rule matches
)

[abrother]

That worked! Thanks so much!