Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi community!
I'm having some trouble setting up a measure.. here's what I have:
Table1 with sales and a field [TicketID] (non-unique, repetitive since each ticket contains multiple items)
Table2 with fields [TicketID] (unique) and [CustomerID] (repetitive)
Relation is built between [TickedID] fields
What we want to achieve: when filtering Table1 (any filter, be it specific product or a date), to return the rows in the Table1 that have the same related [CustomerID] field in Table2
In other words, see what else that customer purchased apart from the already selected rows
I know that it can be achieved by duplicating/referencing the Table1 and then just doing the cross relationships, but I really would like to not do that since it is really the heaviest one, so I wonder if this can be done via DAX within the memory
here's the picture of the process I'm looking for:
step1 (orange) - finding a list of related [CustomerIDs] based on the filtered data in Table1
step2 (green) - finding a list of [TickedIDs] in Table2 that have the same [CustomerIDs] that we've found in step1
step3 (blue) - return the rows in Table1 that have these [TicketIDs]
Thank you!
Solved! Go to Solution.
Hi @alvers ,
One question befor giving a possible solution can you please let me know why do you have a relationship between the customer data and the sales data?
If you remove the relationship and add the following measure you are able to do the selection you need:
Selected Customers =
VAR _SelectedCustomer = SELECTCOLUMNS(
FILTER(
ALL(customer_data),
customer_data[ticket_no] IN VALUES(sales[ticket_no])
),
"Customer", customer_data[customer_no]
)
RETURN
COUNTROWS(
CALCULATETABLE(
customer_data,
customer_data[customer_no] IN _SelectedCustomer
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @alvers ,
This can be done using a measure, however this can be very heavy for the memory usage and the performance may be impacted.
This also depends on the setup of your relationships and interactions between the visuals.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @alvers ,
One question befor giving a possible solution can you please let me know why do you have a relationship between the customer data and the sales data?
If you remove the relationship and add the following measure you are able to do the selection you need:
Selected Customers =
VAR _SelectedCustomer = SELECTCOLUMNS(
FILTER(
ALL(customer_data),
customer_data[ticket_no] IN VALUES(sales[ticket_no])
),
"Customer", customer_data[customer_no]
)
RETURN
COUNTROWS(
CALCULATETABLE(
customer_data,
customer_data[customer_no] IN _SelectedCustomer
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSuper, seems to be working fine, let me try it on the real dataset with high row count...
And If I wanted to calculate anything in the sales table (most measures are refering to it), I need to put one more variable instead of the second part of your formula like
_thisCustomerTickets = selectcolumns (ticket_id) where customer_id in _selectedcustomer
and then use that one as a filter for the main sales table using existing measures and CALCULATE statement, correct?
upd: worked like a charm, at least on a period of couple months (3m rows in sales table and 600k in customers one), takes a couple of seconds to calculate the pre-defined "# of customers" measure
Thank you @MFelix 👍
@alvers ,
I mentioned in the beginning that this would be a very heavy calculation on performance, especially talking about 3M rows and 600K customers, because you need to go over all the rows between both of your tables.
But glad it works.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsJoin the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.