Desktop

[patrick3]

Hi all,

I have unique ID's with multiple payment types - I'm trying to write a formula that'll look at the different values for each ID and return the same value for both rows - i.e. if one ID has the payment types "Payment Scheme" & "Standard", it should return "Payment Scheme" in both rows. There's probably only 3 or 4 different combinations of values

This is my scenario & expected ouput:

Unique ID	Payment Types	Output
1	Payment Scheme	Payment Scheme
1	Standard	Payment Scheme
2	Payment Scheme	Payment Scheme
2	Standard	Payment Scheme
3	Payment Scheme	Payment Scheme
3	Standard	Payment Scheme
4	Refund	Standard
4	Standard	Standard
5	Payment Scheme	Payment Scheme
5	Standard	Payment Scheme
6	Invoice	Invoice
6	Standard	Invoice
7	Refund	Standard
7	Standard	Standard
8	Refund	Standard
8	Standard	Standard
9	Pay4Later	Payment Scheme
9	Payment Scheme	Payment Scheme
9	Refund	Payment Scheme
10	Payment Scheme	Payment Scheme
10	Standard	Payment Scheme
11	Invoice	Invoice
11	Standard	Invoice
12	Invoice	Invoice
12	Standard	Invoice

Can't figure out the DAX for this for the life of me - any tips?

Thanks,

Patrick

[Greg_Deckler · Accepted solution]

See if this works:

Column = 
VAR __table = FILTER(ALL('Table18'),[Unique ID] = EARLIER([Unique ID]))
RETURN
SWITCH(TRUE(),
    CONTAINS(__table,[Payment Types],"Payment Scheme"),"Payment Scheme",
    CONTAINS(__table,[Payment Types],"Invoice"),"Invoice",
    CONTAINS(__table,[Payment Types],"Standard"),"Standard",
    [Payment Types]
)

Table 18 of attached.

working43.pbix

[Greg_Deckler · Accepted solution]

See if this works:

Column = 
VAR __table = FILTER(ALL('Table18'),[Unique ID] = EARLIER([Unique ID]))
RETURN
SWITCH(TRUE(),
    CONTAINS(__table,[Payment Types],"Payment Scheme"),"Payment Scheme",
    CONTAINS(__table,[Payment Types],"Invoice"),"Invoice",
    CONTAINS(__table,[Payment Types],"Standard"),"Standard",
    [Payment Types]
)

Table 18 of attached.

working43.pbix