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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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