Help to build new column with DAX

[AxelKAp]

Hello, 

I have created a column to have a PO lines type, but sometimes people forget to fill some fields so I have some PO lines with a type "No Type", so I want that the line with "No type" takes the type of the other lines of the PO. I show you an example:

PO Number   Line         Comment                 PO Type       Final PO Type
123                   1        I have a quote                P2P                    P2P
123                   2        I have a quote                P2P                    P2P
123                   3             (blank)                      No Type             No type
234                   1        I don't have quote         S2P                      S2P
234                   2        I have quote                   P2P                     S2P

In the example I want to switch No Type with P2P. While knowing I calculate the column "PO Type" like that :

PO Type = IF(CONTAINSSTRING('pochannel-mastervendor'[Form Response],"Je n'ai pas de devis et souhaite un appel d'offre"), "S2P", IF(CONTAINSSTRING('pochannel-mastervendor'[Form Response],"J'ai un devis mais souhaite d'autres propositions"), "S2P", IF(CONTAINSSTRING('pochannel-mastervendor'[Form Response],"J'ai déjà un devis ou plusieurs devis"), "P2P", "No type")))

And the column "Final PO Type" like that :

Final PO Type =

var _POnb = 'pochannel-mastervendor'[PO Number (Header)]

var _list = FILTER('pochannel-mastervendor', 'pochannel-mastervendor'[PO Number (Header)] = _POnb)

Return

IF(CONTAINS(_list,'pochannel-mastervendor'[PO Type] , "S2P"), "S2P", 'pochannel-mastervendor'[PO Type])

Thanks for your help !

 

[VahidDM]

Hi @AxelKAp 

What @lbendlin pointed out is important, you need to add more details to your scenario.

BTW, if you need a new column to show the PO Type and replace NO Type with Final PO Type, use this:

PO Type sum = 
IF (
    [Final PO Type] = "No Type",
    MAXX (
        FILTER (
            'pochannel-mastervendor',
            'pochannel-mastervendor'[PO Number]
                = EARLIER ( 'pochannel-mastervendor'[PO Number] )
                && 'pochannel-mastervendor'[Final PO Type] <> "No Type"
        ),
        'pochannel-mastervendor'[Final PO Type]
    ),
    'pochannel-mastervendor'[Final PO Type]
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[lbendlin]

You need to decide what to do in the following scenarios

- none of the other PO line items has a PO Type either

- the other PO line items have different PO Types