Conditional column between queries

[JesperBT]

Hello everyone,

I would like to create a conditional column that is based on different tables. I know that you are able to merge tables and from there create a conditional column that is based on the new merged column.

However, I would like to minimize the amount of steps needed to do this.

data cfb

Sales Order Creation Date	Sales Order Number	Ship Node	Zip Code
25-09-2021 00:08	1225391512	STO.005	10
26-09-2021 16:25	1227759656	STO.005	11
28-09-2021 12:30	1228115678	STO.094	12
28-09-2021 13:49	1228125685	STO.094	13
27-09-2021 11:41	1227849582	STO.121	14
30-09-2021 12:19	1228660137	CDC.681	15
04-09-2021 16:10	1222558405	STO.172	16
23-09-2021 17:22	1227055015	STO.172	17
16-09-2021 19:11	1225391512	CDC.681	18
26-09-2021 19:41	1227782443	STO.298	19

table pma

Postcode	PMA Number	Region
10	94	East
11	94	East
12	121	East
13	121	East
14	172	East
15	172	East
16	298	East
17	298	East
18	5	East
19	5	East

Result:

table cfb

Sales Order Creation Date	Sales Order Number	Ship Node	Extract Ship Node after delimeter	Zip Code	table pma.PMA Number	DeliveryPMA
25-09-2021 00:08	1225391512	STO.094	94	10	94	1
26-09-2021 16:25	1227759656	STO.005	5	11	94	0
28-09-2021 12:30	1228115678	STO.094	94	12	121	0
28-09-2021 13:49	1228125685	STO.005	5	13	121	0
27-09-2021 11:41	1227849582	STO.121	121	14	172	0
30-09-2021 12:19	1228660137	CDC.681	681	15	172	2
04-09-2021 16:10	1222558405	STO.172	172	16	298	0
23-09-2021 17:22	1227055015	STO.298	298	17	298	1
16-09-2021 19:11	1225391512	CDC.681	681	18	5	2
26-09-2021 19:41	1227782443	STO.298	298	19	5	0

Steps:

Load data cfb and table pma into Power BI

Extract Ship Node after delimeter "."

Merge Queries data cfb.Zip Code with table pma.Postcode

Expand table pma.PMA Number

Create Conditional Column with the logic below

DeliveryPMA Formula

IF Ship Node equals table pma.PMA Number then 1

IF Ship Node equals 681 then 2

IF Ship Node equals 76 then 2

IF Ship Node equals 405 then 2

else 0

Would like if the steps could be cut down to:

Steps:

Load data cfb and table pma into Power BI

Create Conditional Column with the logic above

And just to be sure, this is not possible to do with measures, correct?

Thanks in advance and I appreciate the support

J

[Bassehave · Accepted solution]

@DimaMD 

Thanks a lot for your help. I managed to fiddle a bit with the code you provided and I actually found a way to do it all with a single column.

COM_DeliveryPMA = 
SWITCH(
    TRUE(),
    VALUE(IF(right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)) = "SUPPLIER", "0", right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)))) = LOOKUPVALUE('table pma'[PMA Number],'table pma'[Postcode],'data cfb'[Zip Code]), 1,
    VALUE(IF(right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)) = "SUPPLIER", "0", right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)))) in {681,76,405}, 2,
    0)

The reason for the IF command, is that I found an issue with the Ship Node column, some of the values are "SUPPLIER" which I can't convert to a number.

So I did a VALUE and changed all of the "SUPPLIER" to "0" and then using the LOOKUPVALUE inside of the SWITCH.

This is just the solution I wanted, so thank you very much!

It seems I'm not able to mark your reply as a solution.

[DimaMD]

Hi @JesperBT You provided slightly incorrect data, but according to your expected result it was possible to output your expected result, see the file

[Bassehave]

Hej DimaMD,

Thanks a lot for taking the time to help me, I really appreciate it.

I'm just curious, what data did you find that was incorrect?

I will try to convert it to my original dataset and see the result, but I think it will work.

J

[DimaMD]

Hi, @Bassehave Sales Order Number and Ship Node in the data cfb table do not match with your result that you provided

[Bassehave]

Hej DimaMD,

Good catch! I had to manually change some cells to get a viable dataset on small scale, but as you said, it shouldn't matter when I used the formula in the original file. Thanks a lot!

J