Forum Discussion

jacccodezwart's avatar
3 years ago
Solved

Check if all values exist

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 ha...
  • PaulOlding's avatar
    3 years ago

    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