Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to Solution.
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.
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.
Hi @JesperBT You provided slightly incorrect data, but according to your expected result it was possible to output your expected result, see the file
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
Hi, @Bassehave Sales Order Number and Ship Node in the data cfb table do not match with your result that you provided
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
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |