Forum Discussion

PatrickNahayo's avatar
PatrickNahayo
Frequent Visitor
2 years ago
Solved

product recommendation analytics comparing 2 highest values from different tables

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 ...
@PowerBI Report. @dax
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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]
    )
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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]
)