topic Re: Find cross brand buyers in DAX Commands and Tips

Find cross brand buyers

TutanRamon — Fri, 10 Jan 2025 10:55:22 GMT

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.

Re: Find cross brand buyers

Poojara_D12 — Fri, 10 Jan 2025 11:25:10 GMT

Hi TutanRamon

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

Dynamic Brand Selection: Use SELECTEDVALUE(ecom_brands[Name]) to dynamically get the brand selected in the slicer.
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

Re: Find cross brand buyers

TutanRamon — Fri, 10 Jan 2025 11:48:37 GMT

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?

Re: Find cross brand buyers

TutanRamon — Fri, 10 Jan 2025 12:32:31 GMT

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.

Re: Find cross brand buyers

Anonymous — Mon, 13 Jan 2025 05:27:27 GMT

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.

Re: Find cross brand buyers

Anonymous — Thu, 16 Jan 2025 08:25:13 GMT

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

Re: Find cross brand buyers

sjoerdvn — Thu, 16 Jan 2025 09:52:35 GMT

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.