Need help on a Calculated Column about two related tables.

[dayanday]

New to Power BI, your guidance would be greatly appreciated.

I have 2 related tables. One table has vendor's information, the primary key is vendorID, another is product table which include the vendorID as a foreign key, plus a filed indicating the product's quality level, say good, average and bad. In addition, a vendor may have a few products.

Now I wanted to add a calculated column in Vendor table to label it as good, average and bad. The criteria is based on related Product table, to check the vendor's product, if the vendor has any good product, then I label it as good too; If not, then I check to see if the vendor has any average product, then label it as average; if the vendor doesn't has any good or average product, then I label it as bad.

How should I do this in Power Pivot Tables? Thanks.

 

[scottsen]

There are probably (certainly ) more clever ways to do this, and more performant, but after a long day... I am going with "easiest to explain".

From the the vendor table, you are looking back to the products -- sort of the opposite of the typical lookup (which is from the fact/many side to the lookup/one side). As a calc column on the vendor table you can use RELATEDTABLE() to return all rows for that vendor, from the product table. Of course, since it is a table... you need to do something to aggregate it, say... COUNTROWS().

So, =COUNTROWS(RELATEDTABLE(Products)) is going to give you the count of products for that vendor.

Which is kinda almost what we want, but we only want products that are (say) "good".

=CALCULATE(COUNTROWS(RELATED(Products)), Products[Quality] = "good")

At least, I think that might work. If not, maybe try:
=COUNTROWS(CALCULATETABLE(RELATED(Products), Product[Quality] = "good")

And if that doesn't work, ... pick up a career as a profession whiskey tester.

Hopefully you can take it from there w/ some IF [the stuff above] > 0 ... sorta stuff. If not come yell at me.

 

[dayanday]

Thanks, Scott. I'll try. Have a nice weekend.

 

[dayanday]

It works in DAX. Thanks.
How can I do the same task by using the normal 2 excel tables?

Requirement: the finalLevel of each Vendors is based on the product quality. For example,
(1) For vendor 'test001' , it has two products (bad, bad), so the finalLevel is bad;
(2) For vendor 'test002' , it has three products ( bad, average ,bad), so the finalLevel is average;
(3) For vendor 'test003', it has four products (good, average, bad, bad), so the finalLevel is good;

Don't know how to write the function for FinalLevel column in Table 1.

---

Table 1 - Vendors

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]vendorID[/TD]
[TD]ProductsCount[/TD]
[TD]LevelStringAll?[/TD]
[TD]FinalLevel[/TD]
[/TR]
[TR]
[TD]test001[/TD]
[TD]=COUNTIF(vendors[vendorId], [vendorId])[/TD]
[TD]Do I need this to count the finalLevel?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]test002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]test003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2 - Products
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]productID[/TD]
[TD]VendorID[/TD]
[TD]QualityLevel[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]test002[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]test003[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]test002[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]test001[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]test001[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]test002[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]test003[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]test003[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]test002[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]test003[/TD]
[TD]Bad[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

Hopefully somebody here is good at excel, because it is not me. I power pivot You might end up asking in the "main" forum.

 

[ImkeF]

You can take the matrix-approach with: {MAX(IF…))}

Therefore you need to give your QualityLevel classifications a rank (numerical value). (Simple lookup table that feeds another column in your Products table)

 

[billszysz]

Final level in vendors table (using structural notation)

=INDEX({"Good","Average","Bad"},MATCH(1,--ISNUMBER(MATCH({"Good","Average","Bad"},IF(Products[VendorID]=[@vendorID],Products[QualityLevel]),0)),0))

CTRL+SHIFT+ENTER

Regards