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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ttaylor9870
Helper III
Helper III

Creating a column based on the conditions of another (Figure this out you're a legend)

Hi All,

 

Here is the 2 tables I am working with...

 

Sales Table

Owner (Spec)Owner( Housebuilder)Owner(Commercial)Owner(NAM)RegionEst RevenueEst. Spec Value
 Gary Garyson  Admin£100,000£245,000
  Sam Samson Admin£174,000£290,000
  Sam SamsonRichard RichardsonScotland£155,000£212,000
 James JamesonSam SamsonRichard RichardsonROI£109,000£288,000
 John Johnson Richard RichardsonAdmin£112,000£245,000

 

Now here is my other table...

 

Owner & Region

OwnerRegion
Gary GarysonMidlands
James JamesonROI
John JohnsonNI
Sam SamsonScotland

 

My problem is that some of my sales records in my sales table have the value "Admin" assigned to them in the Region column. I would like a solution on how would I go about assigning the correct Est. Revenue to the correct region? My catch is that I would like the Est. Revenue assigned to the owners region in this order... Owner (Housebuilder) if this is empty then then Owner (Commercial) and if this is empty then finally Owner (NAM).

 

Also this is only a small example of my Sales table and this all assumes that Owner(Spec) has been left blank. Most rows do infact have the Owner(Spec) column with a value in it. I want this solution to work in a way where even if there is Owner(Spec) values in the column aslong as Admin is the region completely disregard this column.

 

Maybe I need a new region column created to show the correct region in the order of assgment I would like?

 

Any help would be greatly appreciated! 🙂

1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
Community Support

Hi @Ttaylor9870 ,

 

According to your deescription, here is my solution.

I created two samples, one is that Owner(Spec) has been left blank and the other one is that Owner(Spec) has values.

Create a column to show the which owner the Est. Revenue will be assigned to according to your order.

 

 

Column =
IF (
    'Sales Table'[Region] = "Admin",
    IF (
        'Sales Table'[Owner( Housebuilder)] <> BLANK (),
        'Sales Table'[Owner( Housebuilder)],
        IF (
            'Sales Table'[Owner(Commercial)] <> BLANK (),
            'Sales Table'[Owner(Commercial)],
            'Sales Table'[Owner(NAM)]
        )
    ),
    IF (
        'Sales Table'[Owner (Spec)] <> BLANK (),
        'Sales Table'[Owner (Spec)],
        IF (
            'Sales Table'[Owner( Housebuilder)] <> BLANK (),
            'Sales Table'[Owner( Housebuilder)],
            IF (
                'Sales Table'[Owner(Commercial)] <> BLANK (),
                'Sales Table'[Owner(Commercial)],
                'Sales Table'[Owner(NAM)]
            )
        )
    )
)

 

 

You need to establish a relationship between two tables.

vxiaosunmsft_0-1666685111402.png

And then create a new column to return the region.

 

 

Region2 =
CALCULATE (
    VALUES ( 'Owner & Region'[Region] ),
    FILTER ( 'Owner & Region', 'Sales Table'[Column] = 'Owner & Region'[Owner] )
)

 

 

Final output:

vxiaosunmsft_1-1666685376946.png

 

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Ttaylor9870
Helper III
Helper III

@v-xiaosun-msft Thank you so much you're class!!! Many many thanks! 🙂

v-xiaosun-msft
Community Support
Community Support

Hi @Ttaylor9870 ,

 

According to your deescription, here is my solution.

I created two samples, one is that Owner(Spec) has been left blank and the other one is that Owner(Spec) has values.

Create a column to show the which owner the Est. Revenue will be assigned to according to your order.

 

 

Column =
IF (
    'Sales Table'[Region] = "Admin",
    IF (
        'Sales Table'[Owner( Housebuilder)] <> BLANK (),
        'Sales Table'[Owner( Housebuilder)],
        IF (
            'Sales Table'[Owner(Commercial)] <> BLANK (),
            'Sales Table'[Owner(Commercial)],
            'Sales Table'[Owner(NAM)]
        )
    ),
    IF (
        'Sales Table'[Owner (Spec)] <> BLANK (),
        'Sales Table'[Owner (Spec)],
        IF (
            'Sales Table'[Owner( Housebuilder)] <> BLANK (),
            'Sales Table'[Owner( Housebuilder)],
            IF (
                'Sales Table'[Owner(Commercial)] <> BLANK (),
                'Sales Table'[Owner(Commercial)],
                'Sales Table'[Owner(NAM)]
            )
        )
    )
)

 

 

You need to establish a relationship between two tables.

vxiaosunmsft_0-1666685111402.png

And then create a new column to return the region.

 

 

Region2 =
CALCULATE (
    VALUES ( 'Owner & Region'[Region] ),
    FILTER ( 'Owner & Region', 'Sales Table'[Column] = 'Owner & Region'[Owner] )
)

 

 

Final output:

vxiaosunmsft_1-1666685376946.png

 

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors