Forum Discussion
Return Value from another table[column] based on weight
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_idBest Regards,
Gao
Community Support TeamIf 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
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
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.
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