Forum Discussion
Solved
Return Value based on Multiple Distinct Values in different column
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
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.
1 Reply
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.