Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JesperBT
Frequent Visitor

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

1 ACCEPTED SOLUTION
Bassehave
Helper I
Helper I

@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.

View solution in original post

5 REPLIES 5
Bassehave
Helper I
Helper I

@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
Solution Sage
Solution Sage

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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
Screenshot_19.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors