Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
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
It will work with text values, you just need to put the fully qualified column name into the VALUES function.
This was the route I was trying to get to, shall try it out. Thank you
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |