Return Value from another table[column] based on weight

[Hussain_R]

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.

Customer Table

Cust_ID	Name	Lookup Value
0001221	Anam
0002022	Megan
0031003	Rania
0010019	Karim

Fact Table

Date	Cust_ID	Prod_ID	Spend
1/1/22	0001221	K.001	12
1/3/22	0002022	K.002	12
12/2/22	0031003	K.003	24
13/2/22	0010019	K.001	36
14/4/22	0001221	K.002	42
13/3/22	0002022	K.004	31

Product Table:

Prod_ID	Prod_Name	Cate
K.001	Credit Card	1
K.002	Auto Loan	3
K.003	Home Loan	4
K.004	Edu Loan	2

Edit: Need a solution that is in DAX as using PQ takes ages for PBI to apply changes.

I want to lookup the value of product ID from fact table in the customer table but which has the highest cate from the product table.

So for example, the highest product ID for Anam will be K.002 even though she has spent on credit card.

[v-cgao-msft · Accepted solution]

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

[Hussain_R]

This works, but when trying to report the count of customers as a matrix/table with area as rows and columns as the product ID, I can't get the unique counts per area. I get the total customer's I have in the list who're subscribed to the products in each cell and not the unique counts pertaining to said area under specific product ID's. 

Area	K.001	K.002	K.003	K.004
North	1	2
South			1
East			3
West				3

The purpose of the above visual is to show area's highest product subscribed by subscribed customer's, which can also be filtered via a slicer. What it currently shows is the total number of subcribing customer's. 

Edit: The highest K.003 has 84 customer's subscribed to said product. Currently, selecting ID K.003 as a slicer filter, it only shows 2 counts for each area whereas 60% came from the north, and the balance came from west. 

[v-cgao-msft]

Hi @Hussain_R ,

this is off the topic of the thread. please consider ending this thread and reopening a new one. thanks.

Best Regards,
Gao
Community Support Team

[Anonymous]

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]

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]

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