topic Re: Check if all values exist in DAX Commands and Tips

Check if all values exist

jacccodezwart — Tue, 27 Sep 2022 11:22:25 GMT

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

Re: Check if all values exist

PaulOlding — Tue, 27 Sep 2022 12:18:23 GMT

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

Re: Check if all values exist

jacccodezwart — Wed, 28 Sep 2022 07:03:23 GMT

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