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 grades have different scales so they need to be put separately)

Table 1

 A=high, D=low  
product idgrade 1qty 1check
1A0 
1B1 
1C2x
1D3x
2A1 
2B2x
2C0 
2D1 

Table 2

 A=high, C=low  
product idgrade 2qty 2check
1A0 
1B4x
1C1 
2A1 
2B3x
2C2x

Expected outcome: 

product idrecommendation
1yes
2no

 

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

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

1 Reply

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