Forum Discussion
jacccodezwart
Helper I
3 years agoCheck 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...
- 3 years ago
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
PaulOlding
Solution Sage
3 years agoHere'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