Forum Discussion

wimsangers's avatar
wimsangers
Icon for Helper I rankHelper I
7 years ago
Solved

Measure to exclude transactions

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's avatar
    Anonymous
    7 years ago
    [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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    [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's avatar
      wimsangers
      Icon for Helper I rankHelper I
      7 years ago

      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 :smileysad:. 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's avatar
        Anonymous
        Not applicable
        7 years ago
        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