cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

Frequent Visitor

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors