Count distinct rows in table 1 that have the same related value in table 2

[alvers]

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!

[MFelix]

Hi @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.

[alvers]

Hi Miguel! thank you for the prompt reaction. here's the mockup file

[MFelix · Accepted 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
                )
			)

[alvers]

Super, 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 👍

[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.