TM1 MDX with AND/OR statement

[hpansar]

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

[declanr]

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

[ardi]

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")}

[Steve Rowe]

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!