Dax to find the producttype purchased by a customer

[Anonymous]

I want to find the producttype for a given customer. I have 3 tables that are as follows:

customerID	customername
1	a
2	b
3	c

ProductID	SalesCustomerID
1000	3
1004	3
1009	3
2000	1
1007	1
1002	1
1005	2
1003	2

ProductID	Productcategory	Producttype
1000	Product1	new
1002	Product1	existing
1003	Product1	new
1004	Product2	existing
1005	Product1	new
1006	Product2	new
1007	Product1	existing
1009	Product1	existing
2000

The customer and sales table are related by customerID-SalescustomerID and the sales and product table are related by productID.

 If the customer has purchased different producttypes then producttype should be 'mixed' else the producttype should be populated. Output excpected:

CustomerID	Customername	Producttype
1	a	existing
2	b	new
3	c	Mixed

Thanks in advance.

[Ashish_Mathur · Accepted solution]

Hi,

In Table2 (Sales), write this calculated column formula

Product Type = related(product[producttype])

Create a Tabel visual and drag the 2 fields from Table1 to the visual.  Write this measure

Measure = if(distincount(Sales[product type])>1,"Mixed",max(Sales[product type]))

Hope this helps.

[Anonymous]

Thanks for your reponse!!!

[Ashish_Mathur]

You are welcome.

[FrankAT]

Hi @Anonymous ,

with your sample data you can do it like this:

Producttypes = 
VAR _Prototype = CONCATENATEX(VALUES(Table3[Producttype]),Table3[Producttype],", ")
VAR _LenFull = LEN(_Prototype) 
VAR _LenShort = LEN(SUBSTITUTE(_Prototype,",",""))
VAR _CountOfPrototypes = _LenFull - _LenShort + 1
RETURN
    IF(_CountOfPrototypes > 1 , "Mixed", _Prototype)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)