Measure to exclude transactions

[wimsangers]

Hello everybody,

I am stuck with my measure. I am trying to calculate our margins per transaction. However I want to exlcude the transaction that have a contract package with the name: PON-laadbundel. With my current measure I am not able to do this. Can anyone help me adjust this measure so that I exclude the PON-laadbundel transactions.

This is the code that I used, with an example of a transaction that contains PON-laadbundel. Every transaction contatins two rows. I substract the amount of 1 row with the other.

test chargecards = CALCULATE(sum(Query1[amount]);Query1[emsp] = "xxx";Query1[breakdown_type] = "chargecard";Query1[evse_id] = BLANK()) - CALCULATE(sum(Query1[amount]);Query1[emsp] = "xxx";Query1[breakdown_type] = "chargepoint";Query1[evse_id] = BLANK())

Thank you in advance,

Wim

[Anonymous]

[Your Measure] =
calculate(
    [test chargecards],
    Query1[contract_package] <> "PON-laadbundel"
)

I'd strongly suggest you give your tables and columns sensible and easily understandable names. Query1 is certainly not a name that's meaningful. If you did something like that in a company that is serious about programming best practices, you'd feel the heat in no time. On another note... I don't know the whole design you've got in there, but if the table below is the sole table in the model... well, I'd re-think several times and try to read about the proper dimensional design. If you want to have an easy time later down the line instead of scratching your head and trying to figure out for hours on end where a figure came from, then you'll take the above advice seriously. If you don't, then don't tell me later I have not warned you.

Best

Darek

[wimsangers]

Hi @Anonymous ,

You are absolutely right about the names that I use. However, this is a test dataset. I am first trying to get the right measures before I import all the data.

Regarding your solution, It still includes the transaction . I think this is because this measure filters out the row that contains the PON-laadbundel, but is does not remove the other row as this one does not contain PON-laadbundel. So I still get a margin of -8,73 for this transaction.

[Anonymous · Accepted solution]

[Your Measure] =
var __transactionIdsToExclude =
	calculatetable (
		values ( Query1[cdr_id] ),
		Query1[contract_package] = "PON-laadbundel"
	)
var __transactionsToKeep = 	
	except (
		values ( Query1[cdr_id] ),
		__transactionIdsToExclude
	)
var __result =
	calculate(
	    [test chargecards],
	    __transactionsToKeep
	)	
return
	__result

Best

Darek

[wimsangers]

Thank you very much @Anonymous.

You've made my day!

Wim

[Anonymous]

Mate, in order to get your problem solved you have to be *very clear* about what it is you need. You said you wanted to exclude the row where Query1[contract_package] <> "PON-laadbundel". This is what the code did.

Best
Darek