Forum Discussion

SBGoh's avatar
SBGoh
Icon for Helper I rankHelper I
2 years ago

Filter rows seems not working in dropdown list

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_NUMCUSTOMERCREATED_ON
190019000025/10/2023
1901281118/05/2023
19023894501/04/2024

 

SO_ITEM

SO_NUMPOS_NUMMATERIALCS_NUM
19000011PartNum 1100001
19000022PartNum 2P20000
19010011PartNum 3P20000
19020010PartNum 410002

 

STATUS

SO_NUMPOS_NUMSTATUS
19000011Complete
19000022 
19010011Complete
19020010Active

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.

 

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      SBGoh
      Icon for Helper I rankHelper I
      2 years ago

      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.