Update a Flag Based on Columns in Two Tables

Question

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.&nbsp;This is the table with the Exclusion criteria (tbl_00_Info_Excl_Rules):IDCarrierProduct3CHAP4&nbsp;TE5&nbsp;AL6TAP&nbsp;7TAF&nbsp;8USL&nbsp;9CN&nbsp;10FT&nbsp;11GC&nbsp;12HT&nbsp;13HTK&nbsp;14MNFADO15MNFREC16MNFRE17SICADO18MNFAD19SCL&nbsp;&nbsp;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:AutoNumberCarrierProductAmountAmount_CategoryFilenameExclude&nbsp;12106109MNFAD99.00PriorReporting-Dec-2023Yscenario 112106110TAFTE999.00PriorReporting-Dec-2023Yscenarios 2 and 312106111&nbsp;GE99.00PriorReporting-Dec-2023&nbsp;&nbsp;12106112SICAD9,999.00PriorReporting-Dec-2023&nbsp;&nbsp;12106113CHAP999.00PriorReporting-Dec-2023Yscenario 112106114TAP&nbsp;99.00PriorReporting-Dec-2023Yscenario 312106115TAPADO999.00PriorReporting-Dec-2023Yscenario 312106116USLHIP999.00PriorReporting-Dec-2023Yscenario 312106117SICADO999.00PriorReporting-Dec-2023Yscenario 112106118&nbsp;AL99.00PriorReporting-Dec-2023Yscenario 212106119CNAD9,999.00PriorReporting-Dec-2023Yscenario 312118736MNFADO99.00PriorReporting-Dec-2023Yscenario 112118737MNF&nbsp;9,999.00PriorReporting-Dec-2023&nbsp;&nbsp;&nbsp;This is "scenario 1", where both columns must match to update the flag to a "Y":&nbsp;This is "scenario 2", where Products match and Carrier "is null":This is "scenario 3", where Carriers match and Product "is null":&nbsp;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.&nbsp; &nbsp;have learned a lot but I am still at a loss.&nbsp; 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.&nbsp; This worked fine for exclusions where both Carrier and Product matched, but created inaccurate results for the other two scenarios.&nbsp; By the way, I did create 3 separate flags in BI so that I could keep track of my results.&nbsp;In my limited understanding, I attempted to use the RELATED function, the USERELATIONSHIP function, and even attempted creating a bridge.&nbsp; All of this was a bit beyond my limited learning and I have been spinning my wheels for some days now.&nbsp; 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.&nbsp;&nbsp;Any help that you can provide will be greatly appreciated.&nbsp; We are in the process of adopting the BI platform and will eventually have many Access databases that we wish to "convert" to BI.&nbsp;Thanks in advance!~Julie&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;JKingFMG&nbsp;,
&nbsp;
Maybe you can try formula like below:
Scenario1 = 
IF (
    NOT ISBLANK ( 'tbl_30_Analysis'[Carrier] )
    &amp;&amp; NOT ISBLANK ( 'tbl_30_Analysis'[Product] ),
    "Y",
    "N"
)

&nbsp;
Best Regards,Adamk Kong
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Related content

Grouping rows by Column and then updating Column

Table UPDATE statements on the same Warehouse table.

Update visual table column via CSV

Calculated table update

Direct Lake Model - Calculated Column/Tables