Find cross brand buyers

[TutanRamon]

I run a webshop and have all the sales data imported in PowerBI. So far, so good, but I want to create a next level visual. I am planning to use Microsoft's Force Directed Graph. Therefore, I need a virtual table

SourceBrand | TargetBrand | Number of Customers

So, I select a brand from the brand list, let's say Sony. Then I want to find the customer count for each other brand they buy as well. This is an example output I desire:
Sony | Philips | 10
Sony | Bosch | 54
Sony | KG | 22
Sony | Plass | 4
etc.

I am struggling with the DAX-code for this. Somehow, the slicer value isn't being used in the calculation. 

BrandCrossPurchasesMeasure =
VAR SelectedBrand = CALCULATE(SELECTEDVALUE(ecom_brands[Name], "Sony"))
VAR SelectedBrandID =
	CALCULATE(
		MIN(ecom_brands[brandId]),
		ecom_brands[Name] = SelectedBrand
)
VAR CustomersOfSelectedBrand =
	CALCULATETABLE(
		DISTINCT(ecom_invoices[customerId]),
		FILTER(
			ecom_sales,
			RELATED(ecom_products[brandId]) = SelectedBrandID
		)
	)

RETURN
	SUMMARIZE(
		VALUES(ecom_brands[Name]),
		ecom_brands[Name], -- TARGET
		"SOURCE", SelectedBrand,
		"CUSTOMERCOUNT",
		COUNTROWS(
			INTERSECT(
				CustomersOfSelectedBrand,
				CALCULATETABLE(
					DISTINCT(ecom_invoices[customerId]),
					FILTER(
						ecom_sales,
						RELATED(ecom_products[brandId]) =
							CALCULATE(
								MIN(ecom_brands[brandId]),
								ecom_brands[Name] = ecom_brands[Name]
							)
					)
				)
			)
		)
	)

Data model:

ecom_customers (customerId, Name etc.)

ecom_products (productId, brandId, price, name etc.)

ecom_sales (salesrow_id, invoiceId, productId, amount, totalprice etc.)  (basically these are the invoice rows)

ecom_brands (brandId, Name etc.)

ecom_invoices (InvoiceId, customerId, totalAmount, tax etc.)

Relations

ecom_customers(customerId) 1: n with ecom_invoices(customerId)

ecom_products (productId) 1:n with ecom_sales(productId)

ecom_products (productId) n:1 with ecom_brands(brandId)

ecom_invoices(invoiceId) 1:n with ob_sales(invoiceId)

Can somebody explain why the selectedBrand isn't used in the calculation. Might have something to do with context, but how to fix this? Because the fallback value "Sony" is working in this case, but it needs to be dynamic.

[sjoerdvn]

Hi,

Not sure what you mean with "virtual table", but if you are creating a calculated table in an import model, that is not going to work. Calculated tables are populated when the model is being processed, so it will not consider any slicers or filters.
So you should either be using measures or create a calculated table with all possible combinations and put that slicer on that table.

[v-xinc-msft]

Hi @TutanRamon ,

Did the reply above offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best regards,

Lucy Chen

[Poojara_D12]

Hi @TutanRamon 

The issue is that the slicer value isn't propagating due to context problems. To fix this:

1. Dynamic Brand Selection: Use SELECTEDVALUE(ecom_brands[Name]) to dynamically get the brand selected in the slicer.

2. Filter Customers: Create a table of customers who bought the selected brand using:

CustomersOfSelectedBrand =
CALCULATETABLE(
    DISTINCT(ecom_invoices[customerId]),
    FILTER(
        ecom_sales,
        RELATED(ecom_products[brandId]) = SelectedBrandID
    )
)

• Find Overlap: Use INTERSECT to count customers who bought both the selected brand and other brands.
• Summarize Results: Build a virtual table excluding the selected brand:

RETURN
SUMMARIZE(
    FILTER(ecom_brands, ecom_brands[Name] <> SelectedBrand),
    ecom_brands[Name],
    "SOURCE", SelectedBrand,
    "CUSTOMERCOUNT",
    COUNTROWS(
        INTERSECT(
            CustomersOfSelectedBrand,
            CALCULATETABLE(
                DISTINCT(ecom_invoices[customerId]),
                FILTER(
                    ecom_sales,
                    RELATED(ecom_products[brandId]) = ecom_brands[brandId]
                )
            )
        )
    )
)
​

• This will dynamically generate a table showing customer counts for cross-brand purchases.

  Did I answer your question? Mark my post as a solution, this will help others!
  If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

  Kind Regards,
  Poojara
  Data Analyst | MSBI Developer | Power BI Consultant
  Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[TutanRamon]

Ok, I now have this:

CustomersOfSelectedBrand = 
VAR SelectedBrand = SELECTEDVALUE(ecom_brands[Name])

VAR SelectedBrandID = 
	CALCULATE(
		MIN(ecom_brands[BrandId]),
		ecom_brands[Name] = SelectedBrand
)
RETURN
CALCULATETABLE(
    DISTINCT(ecom_invoices[CustomerId]),
    FILTER(
        ecom_sales,
        RELATED(ecom_products[BrandID]) = SelectedBrand
    )
)

But, this keeps giving me the same list, regardless the brand i am choosing in the slicer.

[v-xinc-msft]

Thank you for the reply from Poojara_D12 !

Hi @TutanRamon ,

Try to change RELATED(ecom_products[BrandID]) in the FILTER function to 

RELATED(ecom_products[BrandID]) = SelectedBrandID.

If it still doesn't return the results you need, try simplifying the code and troubleshooting the problem step by step. For example, you could start by returning just the value of SelectedBrandID to see if it's correct, and then gradually add the rest. Or could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TutanRamon]

Thanks. I already tried SELECTEDVALUE(ecom_brands[Name]) but that didn't work, so i switched to CALCULATE(x).

Do I understand correctly that you are advising to create a 'separate' table for CustomersOfSelectedBrand? In that piece of code, you use SelectedBrandID. So, I first need to declare that as a VAR?