Update a Flag Based on Columns in Two Tables

[JKingFMG]

Hello,

I am trying to recreate an Access database process in BI Desktop involving 3 separate relationships between two tables (3 Access queries) and resulting in an updated exclusion flag in one of the tables.

This is the table with the Exclusion criteria (tbl_00_Info_Excl_Rules):

ID	Carrier	Product
3	CH	AP
4		TE
5		AL
6	TAP
7	TAF
8	USL
9	CN
10	FT
11	GC
12	HT
13	HTK
14	MNF	ADO
15	MNF	REC
16	MNF	RE
17	SIC	ADO
18	MNF	AD
19	SCL

This is the table where the "Exclude" flag will be updated (tbl_30_Analysis), with the expected result in the "Exclude" column, and a column that I added at the end to show which scenario (shown further below) resulted in the exclusion:

AutoNumber	Carrier	Product	Amount	Amount_Category	Filename	Exclude
12106109	MNF	AD	99.00	Prior	Reporting-Dec-2023	Y	scenario 1
12106110	TAF	TE	999.00	Prior	Reporting-Dec-2023	Y	scenarios 2 and 3
12106111		GE	99.00	Prior	Reporting-Dec-2023
12106112	SIC	AD	9,999.00	Prior	Reporting-Dec-2023
12106113	CH	AP	999.00	Prior	Reporting-Dec-2023	Y	scenario 1
12106114	TAP		99.00	Prior	Reporting-Dec-2023	Y	scenario 3
12106115	TAP	ADO	999.00	Prior	Reporting-Dec-2023	Y	scenario 3
12106116	USL	HIP	999.00	Prior	Reporting-Dec-2023	Y	scenario 3
12106117	SIC	ADO	999.00	Prior	Reporting-Dec-2023	Y	scenario 1
12106118		AL	99.00	Prior	Reporting-Dec-2023	Y	scenario 2
12106119	CN	AD	9,999.00	Prior	Reporting-Dec-2023	Y	scenario 3
12118736	MNF	ADO	99.00	Prior	Reporting-Dec-2023	Y	scenario 1
12118737	MNF		9,999.00	Prior	Reporting-Dec-2023

This is "scenario 1", where both columns must match to update the flag to a "Y":

This is "scenario 2", where Products match and Carrier "is null":

This is "scenario 3", where Carriers match and Product "is null":

I am very new to the BI platform (learning via self-paced tutorials) and have searched for clues in this and other forums for over a week.   have learned a lot but I am still at a loss.  I created a calculated field in both tables in BI that was a concatenation of the Carrier and Product, then created a relationship between the two tables.  This worked fine for exclusions where both Carrier and Product matched, but created inaccurate results for the other two scenarios.  By the way, I did create 3 separate flags in BI so that I could keep track of my results.

In my limited understanding, I attempted to use the RELATED function, the USERELATIONSHIP function, and even attempted creating a bridge.  All of this was a bit beyond my limited learning and I have been spinning my wheels for some days now.  My problem, as I best understand it, is that once I create a relationship with the concatenated columns, it throws off any comparisons I wish to later make between single columns, and vice versa. 

Any help that you can provide will be greatly appreciated.  We are in the process of adopting the BI platform and will eventually have many Access databases that we wish to "convert" to BI.

Thanks in advance!

~Julie 

[Anonymous]

Hi @JKingFMG ,

Maybe you can try formula like below:

Scenario1 = 
IF (
    NOT ISBLANK ( 'tbl_30_Analysis'[Carrier] )
    && NOT ISBLANK ( 'tbl_30_Analysis'[Product] ),
    "Y",
    "N"
)

Best Regards,
Adamk Kong

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