Check if all values exist

[jacccodezwart]

Hi there,

Here is the situation :

I have contractlines, which hold an activity code. It looks like this :

Contractnumber is 4864, and the activity codes are O03 and O29

Now I have orderlines which contain an activity code and an contractnumber, that looks like this :

The question is . . how do I check that ALL the activity-codes from the contract are in the order ? It can be that an order holds more activity codes than the contract, which is okay, but it should return true in that case as well.

All help is welcome

Thanks in advance.

Jacco

[jacccodezwart]

@PaulOlding Thanks for you reply. Implemented it and works like a charm !!

[PaulOlding · Accepted solution]

Hi @jacccodezwart 

Here's one way to do it.  This is a measure that will give you True or False for each OrderKey.

I've assumed your model is 2 disconnected tables

Has All Activity Codes = 
VAR _Orders = 
SELECTCOLUMNS(orderlines, 
    "ContractNumber", orderlines[OrderContractNumber] & "",
    "ActivityKey", orderlines[ActivityKey] & ""
)
VAR _Contracts = 
SELECTCOLUMNS(contractlines, 
    "ContractNumber", contractlines[ContractNumber] & "",
    "ActivityKey", contractlines[ActivityKey] & ""
)
VAR _Joined = 
NATURALINNERJOIN(_Contracts, _Orders)
VAR _ContractActivityCount = 
CALCULATE(
    COUNTROWS(contractlines),
    contractlines[ContractNumber] = SELECTEDVALUE(orderlines[OrderContractNumber])
)
RETURN
    _ContractActivityCount <= COUNTROWS(_Joined)

The '& ""' are there to break lineage, so NATURALINNERJOIN will combine _Orders and _Contracts