product recommendation analytics comparing 2 highest values from different tables

[PatrickNahayo]

Hello, i need to create a check mark for the products recommendation, and this i have tried, with IFs, SUMMARY and calculated tables but with no satisfying results. Here are my tables (note that the grades have different scales so they need to be put separately)

Table 1

	A=high, D=low
product id	grade 1	qty 1	check
1	A	0
1	B	1
1	C	2	x
1	D	3	x
2	A	1
2	B	2	x
2	C	0
2	D	1

Table 2

	A=high, C=low
product id	grade 2	qty 2	check
1	A	0
1	B	4	x
1	C	1
2	A	1
2	B	3	x
2	C	2	x

Expected outcome: 

product id	recommendation
1	yes
2	no

Explanation:

product 1 is recommended because we have in table1 (so we need to get the 2 highest categories taking into account that A is high and D the lowest) grades C and D where quantity is > 2 as the first condition and in table 2 we have at least 1 category with qty > 2 (as the second condition, it can be 1 or more).
product 2 is not recommended because we only have one category in table 1 with qty > 2 and that doesn't quality.

Thanks very much

[bhanu_gautam · Accepted solution]

@PatrickNahayo , First we need to create to calculated column for check in Table 1 and 2

For table 1

Check1 = IF(

    (Table1[grade 1] = "C" || Table1[grade 1] = "D") && Table1[qty 1] > 2,

    "x",

    BLANK()

)

For table 2

Check2 = IF(
Table2[qty 2] > 2,
"x",
BLANK()
)

Then create a measure

Recommendation =
VAR CheckTable1 =
CALCULATE(
COUNTROWS(Table1),
Table1[Check1] = "x"
)
VAR CheckTable2 =
CALCULATE(
COUNTROWS(Table2),
Table2[Check2] = "x"
)
RETURN
IF(
CheckTable1 >= 2 && CheckTable2 >= 1,
"yes",
"no"
)

Then in last create a summary table

SummaryTable =
SUMMARIZE(
Table1,
Table1[product id],
"Recommendation", [Recommendation]
)