Matching a Dimension in a Measure

[waleeed_mahmood]

Hello all,

I have a question i cant seem to figure out. I have a table like below:

I have PI column in a flat table dimension and i am trying to apply the following logic to get the desired output column in the table below:

Desired Output  Formula:

if(match(trim("PI"),'SN','NP','') AND NOT (isnull("F L1") OR trim("F L1")=''),
Avg(Aggr((sum(Future Sim L2 NP) -sum(C L2 NP))/sum(C L2 NP),[SAPM],"MPG")))

NOTE: There is more to this formula but if the first if statement doesnt work, rest isnt valid anyway.

Probelm:

My set analysis gets stuck at the Match(trim(PI),'SN',NP',' ') statement and i cant figure out why. If i remove the IF statement, the measure works fine. If i remove the Match statement, measure works fine. Why cant it find a match in that column when its its that in the text?

Please let me know if you need more information.

Thank you.

PI	MPG	SAPM	F L1	C L2 NP	C L2 SP	F L2 NP	F L2 SP	Future Sim L2 NP	Future Sim L2 SP	DESIRED OUTPUT
NP	K4	6ES71950AA05	180	587		621		621	1000000	5.79%
NP	K4	6ES71950AA15	1,737.00	5,660.00		5,990.00		5990	1000000	5.83%
NP	K4	6ES71950AA25	14,698.00	47,900.00		50,700.00		50700	1000000	5.85%
NP	K4	6ES71950AA35	86,533.00	282,000.00		299,000.00		299000	1000000	6.03%
NP	K4	6ES71950BD04	110	359		380		380	1000000	5.85%
NP	K4	6ES71950BD14	1,093.00	3,560.00		3,770.00		3770	1000000	5.90%
NP	K4	6ES71950BD24	9,024.00	29,400.00		31,100.00		31100	1000000	5.78%
NP	K4	6ES71950BD34	35,615.00	116,000.00		123,000.00		123000	1000000	6.03%
NP	K4	6ES71950BD44	7,200.00	23,500.00		24,800.00		24800	1000000	5.53%
NP	K4	6ES71950BF02	95	310		328		328	1000000	5.81%
NP	K4	6ES71950BF12	868	2,830.00		2,990.00		2990	1000000	5.65%
NP	K4	6ES71950BF22	7,349.00	24,000.00		25,400.00		25400	1000000	5.83%
NP	K4	6ES71950BF32	42,078.00	137,000.00		145,000.00		145000	1000000	5.84%

[chrismarlow]

Glad you got it all sorted.