Combine LOOKUPVALUE with multiple values with IF statement from 4 interconnected tables

[Anonymous]

Hi everyone,

I try to make a connection between a number of four tables. They're connected via each other, but not all connected to each other. Please see how I connected my tables below, all required data fields are displayed underneath the TableName

DataCustomers -> SalesOrders -> SalesProducts <- DataProducts

CustID                      CustID                CustID

                                                            ProductID          ProductID

                                                                                      Sportlabel

Ideally I'd like to add a column to DataCustomers with the type of Sport they do without any steps in between. A customer (CustID) should be labeled as a certain sport in the table DataCustomers, once this customer has bought a product that's labelled with a certain sport. Customers can practice multiple sports, thus I intended to create multiple columns. However, I cannot manage to find a DAX code that's working. 

Thus, I made two additional columns (undesired) in Table SalesProducts:

- Sport = Related(DataProducts[LabelSport])

- CustID = Related(DataCustomers[CustID])

Additionally I tried to add a column in DataCustomers:

- Type1 = IF(LOOKUPVALUE(SalesProducts[Sport],SalesOrders[CustID],DataCustomers[CustID]) = "Type1" , "YES" , "NO" )

error: a table of multiple values was supplied where a single value was expected.

Does anyone know a way to connect all four tables in a single column in table DataCustomers? Or what other steps I need to take before it'll work? 

Let me know.

Thanks in advance.

[d_gosbell · Accepted solution]

Therefore, the first two lookUPVALUE parameters should typically be from the same table. But even fixing that is not going to help because as you have already pointed out a client may be associated with more than one sport and LOOKUPVALUE only works with a single value.

Maybe I could write a column like the following (I hope this is correct since I haven't tried it)

Type 1 = 
var _type1Products = GROUPBY( FILTER( DataProducts , LabelSport = "Type 1"), DataProducts[ProductID] )
var _sportSales = FILTER( 
	RELATEDTABLE( SalesProducts )
	, SalesProducts[ProductID] IN _type1Products
	)
return if( COUNTROWS( _sportSales ) > 0, "Yes", "No")

However, it is not normally normally necessary to pivot column values like this. What kind of analysis are you trying to do?

If you want to count the number of customers per sport, you could create a measure like the following:

Distinct Customers á DISTINCTCOUNT( SalesProducts[CustID] )

And then you should be able to drag this measurement and the LabelSport column to a visualization to get a customer count for each sport.

[Anonymous]

@d_gosbell Thanks a lot! The code works perfectly fine 😄