TM1 Forum

Hi Everyone,

Im trying to include another piece to my MDX statement that has a measure called "Exclude". So far my MDX statement is as as follows:

{FILTER({TM1SUBSETALL([PPD_Fixed Cost Account])}, [PPD_Fixed Cost Mapping].( [FPS_Department].[161003], [PPD_Fixed Cost Mapping Measure].[BLI Flag]) >0)}

I also want to add to the MDX that has [PPD_Fixed Cost Mapping Measure].[Exclude] <> 'Y'. Im assuming there is an AND or an OR statement. I am stuck so any help would be much appreciated.

thanks

Hiten

hpansar wrote:Hi Everyone,

Im trying to include another piece to my MDX statement that has a measure called "Exclude". So far my MDX statement is as as follows:

{FILTER({TM1SUBSETALL([PPD_Fixed Cost Account])}, [PPD_Fixed Cost Mapping].( [FPS_Department].[161003], [PPD_Fixed Cost Mapping Measure].[BLI Flag]) >0)}

I also want to add to the MDX that has [PPD_Fixed Cost Mapping Measure].[Exclude] <> 'Y'. Im assuming there is an AND or an OR statement. I am stuck so any help would be much appreciated.

thanks

Hiten

For "AND" you just wrap the extra filter by around it (or inside it) etc.

For "OR" you can use union. It is also discussed quite extensively on this forum.


Start reading this for reference:
http://www.bihints.com/book/export/html/68

hpansar wrote:Hi Everyone,

Im trying to include another piece to my MDX statement that has a measure called "Exclude". So far my MDX statement is as as follows:

{FILTER({TM1SUBSETALL([PPD_Fixed Cost Account])}, [PPD_Fixed Cost Mapping].( [FPS_Department].[161003], [PPD_Fixed Cost Mapping Measure].[BLI Flag]) >0)}

I also want to add to the MDX that has [PPD_Fixed Cost Mapping Measure].[Exclude] <> 'Y'. Im assuming there is an AND or an OR statement. I am stuck so any help would be much appreciated.

thanks

Hiten

Did you try:

{FILTER({TM1SUBSETALL([PPD_Fixed Cost Account])}, [PPD_Fixed Cost Mapping].( [FPS_Department].[161003], [PPD_Fixed Cost Mapping Measure].[BLI Flag]) >0 OR [PPD_Fixed Cost Mapping].( [FPS_Department].[161003], [PPD_Fixed Cost Mapping Measure].[Exclude]) <> "Y")}

I've found Except really useful in this type of situation allowing you to subtract one subset from another.
The MDX primer is (should be) required reading for any developer, an excellent piece of work!