Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

If column equals then use value from another table

‎07-11-2024 06:49 AM

I have two tables with similar levels of data, and I want to run an IF formula to determin which table to pull the value from.

The two tables have a many-many relationship on Intersection (Although in my example it could be a many-one).

I effectively want the following formula, but I know that the logicaltest wont work against a column value, so any help to point in the right direction.

 

 

IF MEASURE = IF ( OR (ABS[Group], Forecast[GROUP]) = "GP1",  ABS[Value], Forecast[Value] )

 

 

Both ABS[Value] and Forecast[Value] have already been made into measures aswell.

 

Additionally, how to I make it so that the table has all the unique intersection values from both original tables.

 

Table 1 = Forecast

IntersectionGroupAreaSKUSubmissionDateValue
GP1 | AA1 | SKU01GP1AA1SKU0101/01/202401/01/20241
GP1 | AA1 | SKU02GP1AA1SKU0201/01/202401/01/20242
GP1 | AA2 | SKU02GP1AA2SKU0201/01/202401/01/20245
GP1 | AA2 | SKU03GP1AA2SKU0301/01/202401/01/20246
GP1 | AA3 | SKU01GP1AA3SKU0101/01/202401/01/20247
GP1 | AA3 | SKU03GP1AA3SKU0301/01/202401/01/20249
GP2 | AA1 | SKU01GP2AA1SKU0101/01/202401/01/202410
GP2 | AA1 | SKU02GP2AA1SKU0201/01/202401/01/202411
GP2 | AA1 | SKU03GP2AA1SKU0301/01/202401/01/202412
GP2 | AA2 | SKU01GP2AA2SKU0101/01/202401/01/202413
GP2 | AA2 | SKU02GP2AA2SKU0201/01/202401/01/202414
GP2 | AA2 | SKU03GP2AA2SKU0301/01/202401/01/202415
GP2 | AA3 | SKU01GP2AA3SKU0101/01/202401/01/202416
GP2 | AA3 | SKU02GP2AA3SKU0201/01/202401/01/202417
GP2 | AA3 | SKU03GP2AA3SKU0301/01/202401/01/202418
GP1 | AA1 | SKU01GP1AA1SKU0101/12/202301/01/202451
GP1 | AA1 | SKU02GP1AA1SKU0201/12/202301/01/202452
GP1 | AA2 | SKU02GP1AA2SKU0201/12/202301/01/202455
GP1 | AA2 | SKU03GP1AA2SKU0301/12/202301/01/202456
GP1 | AA3 | SKU01GP1AA3SKU0101/12/202301/01/202457
GP1 | AA3 | SKU03GP1AA3SKU0301/12/202301/01/202459
GP2 | AA1 | SKU01GP2AA1SKU0101/12/202301/01/202460
GP2 | AA1 | SKU02GP2AA1SKU0201/12/202301/01/202461
GP2 | AA1 | SKU03GP2AA1SKU0301/12/202301/01/202462
GP2 | AA2 | SKU01GP2AA2SKU0101/12/202301/01/202463
GP2 | AA2 | SKU02GP2AA2SKU0201/12/202301/01/202464
GP2 | AA2 | SKU03GP2AA2SKU0301/12/202301/01/202465
GP2 | AA3 | SKU01GP2AA3SKU0101/12/202301/01/202466
GP2 | AA3 | SKU02GP2AA3SKU0201/12/202301/01/202467
GP2 | AA3 | SKU03GP2AA3SKU0301/12/202301/01/202468

 

Table 2 = ABP

IntersectionGroupAreaSKUDateValue
GP1 | AA1 | SKU02GP1AA1SKU0201/01/2024102
GP1 | AA1 | SKU03GP1AA1SKU0301/01/2024103
GP1 | AA2 | SKU01GP1AA2SKU0101/01/2024104
GP1 | AA2 | SKU02GP1AA2SKU0201/01/2024105
GP1 | AA2 | SKU03GP1AA2SKU0301/01/2024106
GP1 | AA3 | SKU01GP1AA3SKU0101/01/2024107
GP1 | AA3 | SKU02GP1AA3SKU0201/01/2024108
GP1 | AA3 | SKU04GP1AA3SKU0401/01/2024110

 

Expected Output Table

IntersectionABPValueIF ELSE
GP1 | AA1 | SKU01 10
GP1 | AA1 | SKU021022102
GP1 | AA1 | SKU03103 103
GP1 | AA2 | SKU01104 104
GP1 | AA2 | SKU021055105
GP1 | AA2 | SKU031066106
GP1 | AA3 | SKU011077107
GP1 | AA3 | SKU02108 108
GP1 | AA3 | SKU03 90
GP1 | AA3 | SKU04110 110
GP2 | AA1 | SKU01 1010
GP2 | AA1 | SKU02 1111
GP2 | AA1 | SKU03 1212
GP2 | AA2 | SKU01 1313
GP2 | AA2 | SKU02 1414
GP2 | AA2 | SKU03 1515
GP2 | AA3 | SKU01 1616
GP2 | AA3 | SKU02 1717
GP2 | AA3 | SKU03 1818

 

 

Google Drive Link to PBI Sample 

Labels:
Message 1 of 3
390 Views
0
1 ACCEPTED SOLUTION
barritown
Super User
Super User

‎07-11-2024 02:12 PM

Hi @Anonymous,

 

I'd create an additional table with intersections instead of many-to-many relationship:

barritown_0-1720732040918.png

 

 Then using the only column from the new table and the measure on the screenshot below you can achieve the result you expect:

 

barritown_1-1720732260336.png

 

Please check the attached file with this solution.

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

IF ABP ELSE FCST.pbix
Message 2 of 3
345 Views
2 REPLIES 2
Anonymous
Not applicable

‎07-14-2024 05:46 PM

Hi @Anonymous ,

Based on the description, @barritown provide reply should be helpful.

If you have solved the problem, please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Wisdom Wu

Message 3 of 3
303 Views
0
barritown
Super User
Super User

‎07-11-2024 02:12 PM

Hi @Anonymous,

 

I'd create an additional table with intersections instead of many-to-many relationship:

barritown_0-1720732040918.png

 

 Then using the only column from the new table and the measure on the screenshot below you can achieve the result you expect:

 

barritown_1-1720732260336.png

 

Please check the attached file with this solution.

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

IF ABP ELSE FCST.pbix
Message 2 of 3
346 Views

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All