Concatenate two fields from different columns and exclude

[Daniel1908]

Hello Together, 

I want to  concatenate two fields from different columns and exclude.

Exclude Return Label C with Material 6TZ8*

=Count({<[Price]*={"<=100"},Productkey={'TH',"LR"},[ReturnLabel.]-={'A','B'}, Material= {"6*"}>*(1-<[Request]={"*"}>)}[Product Number])

Thanks for your help. 

Kind Regards, 
Daniel

[jwjackso]

Check the E() function

[Daniel1908]

Can you give me an example how to use the E function in the formula?

Exclude Return Label C with Material 6TZ8*

=Count({<[Price]*={"<=100"},Productkey={'TH',"LR"},[ReturnLabel.]-={'A','B'}, Material= {"6*"}>*(1-<[Request]={"*"}>)}[Product Number])

[jwjackso]

I used [Product Number]=E(), you will need to use a field that is unique to the record.

=Count({<[Price]*={"<=100"},Productkey={'TH','LR'},[Product Number]=E({<[ReturnLabel={'A','B'},Material={"6*"}>}[Product Number])>}[Product Number])

[Daniel1908]

I tried to use as below, unfortunately without success. 

Any Ideas? 

=Count({<[Price]**={"<=100"},Productkey={'TH','LR'},[Returnlabel]-={'A','B'},
Material= {"6*"},Material&Shorttext=E({<[ReturnLabel]={'C'},[Group]={"V231"}>}Material&Shorttext)>},Material&Shorttext)>*(1-<[Request]={"*"}>)}Material&Shorttext)

[Daniel1908]

Hello Together, 

does any one have an idea why Material-={"1RK3*"} doese not work?

=Count( {<Preis]*={"<=100"},Productkey={'TH', 'LR'},[Returnlabel]-={'A','B'}, Material= {"1*"},Material-={"1RK3*"}>*(1-<[Request]={"*"}>)}Material&Shorttext)

Kind Regards
Daniel

[jwjackso · Accepted solution]

Try replacing Material={"1*"},Material-={"1RK3*"} with

Material=P({<Material={"1*"}>}Material)*E({<Material={"1RK3*"}>}Material)

Which should be an intersection of records where Material = "1*" and Material <> "1RK3*"

[Daniel1908]

Thanks a lot that worked. 

Kind Regards
Daniel