Filter rows seems not working in dropdown list

[SBGoh]

Hi, 

I have 3 Sales order tables below and they have the following relationship:

SO_HEADER.SO_NUM = SO_ITEM.SO_NUM (1 to Many)

SO_ITEM.SO_NUM = STATUS.SO_NUM and SO_ITEM.POS_NUM = STATUS.POS_NUM (1 to1)

SO_HEADER

SO_NUM	CUSTOMER	CREATED_ON
1900	190000	25/10/2023
1901	2811	18/05/2023
1902	38945	01/04/2024

SO_ITEM

SO_NUM	POS_NUM	MATERIAL	CS_NUM
1900	0011	PartNum 1	100001
1900	0022	PartNum 2	P20000
1901	0011	PartNum 3	P20000
1902	0010	PartNum 4	10002

STATUS

SO_NUM	POS_NUM	STATUS
1900	0011	Complete
1900	0022
1901	0011	Complete
1902	0010	Active

In power query, I filter STATUS table by STATUS != 'Complete'

But when I dragged "SO_ITEM.MATERIAL" to a dropdown list, it display all part numbers 1,2,3,4. I only expect part number 2 and 4 is displayed. Any idea why is all part number is displayed ? What have I done wrong? Thanks in advance.

[amitchandak]

@SBGoh , Create a new concatenated column in the Item and status tables and join those

New column in DAX

Key = [SO_NUM] & "-" & [POS_NUM]

[SBGoh]

Hi @amitchandak , yes I actually create a composite key that concatenate the 2 fields [SO_NUM] & "-" & [POS_NUM] in both tables. I create a 1:1 relationship. It is incorrect ? Table SO_ITEM has more records because only table STATUS is filtered.

[amitchandak]

@SBGoh , Change that 1- Many from Status to item, it should work

[SBGoh]

I tried single and both direction, seems filtering is not working. ☹️

[SBGoh]

Then the direction should be single or both ?