I have two tables
table 1 "statuses"
status | complete | customer_contact
sale | Y | Y
nosale | N | Y
other | N | N
table 2 "list"
status | DMC
sale |
sale |
nosale |
sale |
table 1 has 40 rows, one for each status, there are no duplicates; table 2 has ~1000000 rows that could be any one of those 40 statuses.
In the query editor* I want to populate DMC in table 2 with a Y or N where the 'status' matches the 'status' in table 1 and complete and customer_contact both = "Y" (just sale in the example)
* I realise that perhaps doing this in M isn't the right approach, is this easier in DAX?
I had expected to use a conditional column but seemingly that doesn't work across tables?
The following works if I add it in table 1 'statuses'
= Table.AddColumn(statuses, "DMC", each if [status] = [status] and [customer_contact] = "Y" and [completed] = "Y" then "Y" else "N")
(I realise that in the above example the first clause is not not needed but i've got it in there as it demonstrates that the logic works when in table 1)
if I modify that and add it to table 2 nothing happens.
= Table.AddColumn(list, "DMC", each if list[status] = statuses[status] and statuses[completed] = "Y" and statuses[customer_contact] = "Y" then "Y" else "N")
I'm presented with a grey screen that just shows the query. I've tried to go around this in many ways and had a whole bunch of different errors but this is where I'm at, and I just can't get anything to work, the above is the clearest way to describe what I'm hoping to do, I think.
Am I just going about this the wrong way?
I'm thinking given the difficulties I'm having it'd be better to leave the DMC column in the statuses, as that works, and then consume this in DAX, but I've tried that too and can't get it to work either. In the spirit of learning though I'd like to understand if this is possible or not.
