Multiple Condition If Statements

[G_Whit-UK]

Hi,

I'm attempting to write a DAX IF statement with multiple conditions using data from two tables.

My primary data set has a list of transaction IDs together with a month end date.  I then have two other related data tables from vendors where they list a subset of the transaction IDs, the same month end date reference and a column denoting the vendor's ID.  I want to create two kinds of measures:

1. A measure which identifies those transaction which match between the primary data set and that of a vendor file (this will need two measures - one per vendor);
2. A measure which identifies any transactions which are not listed on either of the vendor files.

My source data can be represented as follows:

Primary Data:

Transaction ID	ME Date
1	31-Aug-21
2	31-Aug-21
3	31-Aug-21
4	31-Aug-21
5	31-Aug-21
6	31-Aug-21
7	31-Aug-21
8	31-Aug-21
9	31-Aug-21
10	31-Aug-21

Vendor1 Data & Vendor2 Data (seperate tables)

Vendor 1				Vendor 2
Transaction ID	ME Date	Vendor ID		Transaction ID	ME Date	Vendor ID
1	31-Aug-21	Vendor 1		2	31-Aug-21	Vendor 2
3	31-Aug-21	Vendor 1		4	31-Aug-21	Vendor 2
5	31-Aug-21	Vendor 1		6	31-Aug-21	Vendor 2
7	31-Aug-21	Vendor 1		8	31-Aug-21	Vendor 2
9	31-Aug-21	Vendor 1

In the "Relationships" view, I have linked the IDs from the Primary table to the two vendor tables (many to many, both directions) as the active link, then the ME Data fields (also many to many, both directions).

My attemp at the DAX formula to pull out the matching transactions:

Vendor1 Rec =
IF ( and(
     'Primary Data'[Transaction ID] = 'Vendor1'[Transaction ID],
      RELATED ( 'primary Data'[ME Date] ) = 'Vendor1'[ME Date]),
'Vendor1'[Vendord ID],
BLANK ()
)

What is the correct syntax to use for the above, and what would it look like to achieve my 2nd reqirement where I need to find the transactions which do not appear in either of the vendor's files (i.e. transaction ID 10 isn't in either file)?

Thanks.

[G_Whit-UK · Accepted solution]

Here is the solution to the above problem statement.  This is a multi step solution:

Step 1:  Power Query
Edit the query underpinning the Primary Data table by creating a new column and merging the "ME Date" and "Transaction ID" fields. 

=Text.Combine({Text.From([Date], "en-GB"), "-", Text.From([Transaction ID], "en-GB")})

As this should be a unique value, this can be further refined by removing any erroneous duplicates.

Repeat the above process for the "Vendor1" and "Vendor2" tables.

Step 2: Desktop
Now that we have unique references in all source tables, we can create a one to one link between the Primary Data table and the two vendor data tables.

With new tables relationships established, we can now easily add new columns to the Primary Data table using DAX (one column per vendor data table) using the formula: 

Vendor1 Tag = RELATED('Vendor1 Data'[Vendor ID])

We can use similar logic to create a third column to identify any unmatched items using the following DAX formula:

Not Found =
IF (
'Primary Data'[Vendor1 Tag] = BLANK ()
&& 'Primary Data'[Vendor2 Tag] = BLANK (),
"Not found",
BLANK ()
)

[Greg_Deckler]

@G_Whit-UK Well, if it is a measure, you need to use aggregations around column references. 

[G_Whit-UK]

@Greg_Deckler , then perhaps a measure is not the correct solution.  Perhaps I need to add 2 columns to the Primary Data table pulling in the Vendor ID into the respective columns, then a 3rd column to denote any transactions which are blank.

What would be the correct DAX code to acheive the above?

Thanks?

[Greg_Deckler]

@G_Whit-UK Well, your other problem is that RELATED won't work across a many-to-many relationship. There is a lot of unknowns here. I recommend that you provide more sample data or a sample PBIX that can fully replicate your situation. That's the only way to test and really be sure of a solution.