cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TGB
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
d_gosbell
Super User II
Super User II

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.

View solution in original post

2 REPLIES 2
d_gosbell
Super User II
Super User II

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.

View solution in original post

TGB
Frequent Visitor

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.