Filter one large table in variables to then join later in measure

[Anonymous]

Hi,

I have a large table of data on purchases for insurance policies, added-products etc and wondered if there was a way to create two variables to filter the table and then join them on a specific column all within one measure using variables.

For example:
VAR A = a filtered list of records for policies where the transaction type = "New Policy"
VAR B = a filtered list of records where the transaction type = "added product"
VAR C = Inner join of these two variable tables, on their policy number (foreign key for VAR B generated table and primary key for VAR A generated table) so that I can have a new table of added products that were from new policies.

One new policy may have multiple added products. Also, a renewed policy may have purchased added products so I wish to remove these, hence the inner join with the new policy variable.

Apologies if I've not explained very well, but any help is appreciated.

[johnt75 · Accepted solution]

You don't need to do an inner join as such, you can use the filtered list of new policies as an additional filter when filtering for added products, e.g.

Added products for new policies =
VAR NewPolicies =
    CALCULATETABLE (
        VALUES ( 'Table'[Policy number] ),
        'Table'[Transaction type] = "new policy"
    )
VAR AddedProducts =
    CALCULATETABLE (
        'Table',
        NewPolicies,
        'Table'[Transaction type] = "added product"
    )
RETURN
    AddedProducts

Of course, you don't have to return the AddedProducts variable, you could use that in a SUMX or other aggregation.

[Anonymous]

This didn't work as you typed, I get the error: "The VALUES function expects a column reference expression or a table reference expression for argument '1". Possibly because my policy numbers are of text format? If so, they have to be text due to some having letters within them e.g. AB123456

[johnt75]

It will work with text values, you just need to put the fully qualified column name into the VALUES function.

[Anonymous]

This was the route I was trying to get to, shall try it out. Thank you