Forum Discussion
How to apply multiple arguments in a Lookup or Related statement
I am a new learner to PowerBI via UDEMY classes. I am stuck on retrieving a value from a one to many relationship when there are multiple arguments in the filter criteria.
I have 2 tables: SALES and SALES ALLOCATION. The SALES table contains a list of sales transactions. Each Sales transaction is listed once and has a unique DEALID. The SALES ALLOCATION table contains a number of services and allocations which will comprise the total sale. Each DealID/ServiceID combination is a unique entry in the SALES ALLOCATION table. There is a one to many relationship between SALES and SALES ALLOCATION using DealID as the key.
My puzzlement is how to retrieve a unique ServiceAllocationAmt value for a single DealID. The basic examples I have found in forums for RELATED or LOOKUP specify a single argument to join on. How would I accomplish this when I have a parameter value (e.g., "001") that I want to apply to the ServiceID column? This has got to be a simple answer that I have not learned yet.
If this were a SQL statement I would write something like ...
Select ServiceAllocationAmt from [SALES ALLOCATION] where [SALES ALLOCATION].DEALID = SALES.DEALID and [SALES ALLOCATION].ServiceID = "001"
Table examples:
SALES
-DealID ... alpha-numeric
-Account ... text
-DealDescription ... text
-TotalSale numeric ...
SALES ALLOCATION
-DealID ... alpha-numeric
-ServiceID ... text
-ServiceName ... text
-ServiceAllocationAmt ... numeric
Thanks. This did answer the question. Below is the syntax for other self-learners. I created a column named Alloc in the Sales table using the below:
Alloc = LOOKUPVALUE(SalesAllocation[ServicAllocationAmt],
SalesAllocation[DealID],Sales[DealID],
SalesAllocation[ServiceID],"CDT", blank())How would this read if I wanted to express an aditional value for the SalesAllocation[ServiceID]. Suppose I wanted to say both "CDT" and "ADT" met the criteria. What would be that syntax?
2 Replies
- Greg_Deckler
Community Champion
Donnyboy LOOKUPVALUE supports multiple search arguments: LOOKUPVALUE function (DAX) - DAX | Microsoft Learn. Also, you can achieve the same thing using MAXX(FILTER(...),...)
Thanks. This did answer the question. Below is the syntax for other self-learners. I created a column named Alloc in the Sales table using the below:
Alloc = LOOKUPVALUE(SalesAllocation[ServicAllocationAmt],
SalesAllocation[DealID],Sales[DealID],
SalesAllocation[ServiceID],"CDT", blank())How would this read if I wanted to express an aditional value for the SalesAllocation[ServiceID]. Suppose I wanted to say both "CDT" and "ADT" met the criteria. What would be that syntax?
