Forum Discussion

jacccodezwart's avatar
jacccodezwart
Helper I
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 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

 

DAX
power bi
SSAS Tabular
  • PaulOlding's avatar
    PaulOlding
    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

2 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    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