Forum Discussion

Hussain_R's avatar
Hussain_R
Frequent Visitor
3 years ago
Solved

Return Value from another table[column] based on weight

I have a table where I want to lookup a value from another table, but I want to lookup the value that I want to pick has a weight and it needs to return the value with the highest weight behind it. ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Hussain_R ,

     

    Please new a calculated column in Customer Table:

    Lookup Value =
VAR _cust_id = 'Customer'[Cust_ID]
VAR _table =
    CALCULATETABLE ( VALUES ( 'Fact'[Prod_ID] ), 'Fact'[Cust_ID] = _cust_id )
VAR _max_cate =
    CALCULATE ( MAX ( 'Product'[Cate] ), 'Product'[Prod_ID] IN _table )
VAR _prod_id =
    CALCULATE ( MAX ( 'Product'[Prod_ID] ), 'Product'[Cate] = _max_cate )
RETURN
    _prod_id

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Hussain,

 

Please find the code

lkup =
var __step1=calculate(max('Fact'[Prod_ID]),ALLEXCEPT(Customer,Customer[Cust_ID]))
var result= calculate(max('Product'[Cate]),'Product'[Prod_ID]=__step1)
return
result
Hussain_R's avatar
Hussain_R
Frequent Visitor
3 years ago

Wouldn't I be comparing the text data with integer as the product ID is type text and PBI throw an error?

 

Edit: It does. It says:

 

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    It sent me no error. I builyt a file with your datas and it works