Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
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
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |