topic Re: Filter one large table in variables to then join later in measure in DAX Commands and Tips

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

Anonymous — Tue, 19 Sep 2023 11:00:20 GMT

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.

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

johnt75 — Tue, 19 Sep 2023 13:26:31 GMT

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.

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

Anonymous — Tue, 19 Sep 2023 13:50:56 GMT

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

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

Anonymous — Wed, 20 Sep 2023 08:21:43 GMT

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

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

johnt75 — Wed, 20 Sep 2023 09:00:10 GMT

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