Forum Discussion

JesperBT's avatar
JesperBT
Frequent Visitor
3 years ago
Solved

Conditional column between queries

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 DateSales Order NumberShip NodeZip Code
25-09-2021 00:081225391512STO.00510
26-09-2021 16:251227759656STO.00511
28-09-2021 12:301228115678STO.09412
28-09-2021 13:491228125685STO.09413
27-09-2021 11:411227849582STO.12114
30-09-2021 12:191228660137CDC.68115
04-09-2021 16:101222558405STO.17216
23-09-2021 17:221227055015STO.17217
16-09-2021 19:111225391512CDC.68118
26-09-2021 19:411227782443STO.29819

 

table pma

PostcodePMA NumberRegion
1094East
1194East
12121East
13121East
14172East
15172East
16298East
17298East
185East
195East

 

Result:

table cfb

Sales Order Creation DateSales Order NumberShip NodeExtract Ship Node after delimeterZip Codetable pma.PMA NumberDeliveryPMA
25-09-2021 00:081225391512STO.0949410941
26-09-2021 16:251227759656STO.005511940
28-09-2021 12:301228115678STO.09494121210
28-09-2021 13:491228125685STO.0055131210
27-09-2021 11:411227849582STO.121121141720
30-09-2021 12:191228660137CDC.681681151722
04-09-2021 16:101222558405STO.172172162980
23-09-2021 17:221227055015STO.298298172981
16-09-2021 19:111225391512CDC.6816811852
26-09-2021 19:411227782443STO.2982981950

 

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's avatar
    Bassehave
    3 years ago

    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.

5 Replies

    • Bassehave's avatar
      Bassehave
      Helper I
      3 years ago

      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's avatar
        DimaMD
        Solution Sage
        3 years ago

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

  • Bassehave's avatar
    Bassehave
    Helper I
    3 years ago

    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.