Introduction
This article explains the filter conditions in MDX queries. There are so many conditions in MDX since we have SQL. I am explaining the most commonly used filter conditions in MDX. I hope you will like it.
Please see this article in my blog: Filters in MDX Query.
Background
For the past few days, I have been working on the MDX queries. Since my application's data source was an ADOMD data source, it was a must to learn about MDX queries. If you are new to ADOMD, you can find out some tips here: ADOMD Tips.
MDX
Before beginning, we will see what MDX is. The following describes it.
- MDX stands for Multidimensional Expression.
- It is a query language for OLAP databases like SQL for relational databases.
- It is also a calculation language.
- Its syntax is similar to spreadsheet formulas.
If you are completely new to MDX, you can read the basics here.
Expressions and Equivalent in MDX
Now we will use these conditions in the MDX queries. I hope you are aware of the MDX query basics now.
MDX Filter Expression Examples
To check whether a dimension value is empty
To check whether a dimension value is empty or not, you need to add a filter condition as follows.
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Trim([My Dimension Group].[Dimension Name].CurrentMember.Name) = ''
)
To check whether a dimension value is not empty
To check whether a dimension value is empty or not, you need to add a filter condition as follows.
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Trim([My Dimension Group].[Dimension Name].CurrentMember.Name) <> ''
)
To check whether a dimension value contains a specific value
To check whether a dimension value contains a specific value, you need to add a filter condition as follows.
{FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Instr([My Dimension Group].[Dimension Name].CurrentMember.Name, 'My String Value') > 0
)}
To check whether a dimension value does not contain a specific value
To check whether a dimension value does not contain a specific value, you need to add a filter condition as follows.
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Instr([My Dimension Group].[Dimension Name].CurrentMember.Name, 'My String Value') > 0
)
}
To check whether a dimension value starts with a specific value
To check whether a dimension value starts with a specific value, you need to add a filter condition as follows.
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Left([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = 'My String Value'
)
}
To check whether a dimension value ends with a specific value
To check whether a dimension value ends with a specific value, you need to add a filter condition as follows.
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Right([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = 'My String Value'
)
}
To check whether a dimension value equals a specific value
To check whether a dimension value is equal to a specific value, you need to add a filter condition as follows.
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
[My Dimension Group].[Dimension Name].CurrentMember.Name = 'My String Value Carolina'
)
}
To check whether a dimension value is null
To check whether a dimension value is null, you need to add a filter condition as follows.
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
[Measures].[Measure Name] = NULL
)
}
To check whether a dimension value is not null
To check whether a dimension value is not null, you need to add a filter condition as follows.
NON EMPTY([My Dimension Group].[Dimension Name].[Dimension Name])
Examples
The following are examples of queries that use the preceding expressions.
Query 1
SELECT
{ [Measures].[Measure Name] } ON COLUMNS,
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Trim([My Dimension Group].[Dimension Name].CurrentMember.Name) = ''
)
} ON ROWS
FROM
[My Cube Name]
Query 2
SELECT
{ [Measures].[Measure Name] } ON COLUMNS,
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Instr([My Dimension Group].[Dimension Name].CurrentMember.Name, 'My String Value') > 0
)
} ON ROWS
FROM
[My Cube Name]
Query 3
SELECT
{ [Measures].[Measure Name] } ON COLUMNS,
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Left([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = 'My String Value'
)
} ON ROWS
FROM
[My Cube Name]
Query 4
SELECT
{ [Measures].[Measure Name] } ON COLUMNS,
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
Right([My Dimension Group].[Dimension Name].CurrentMember.Name, 5) = 'My String Value'
)
} ON ROWS
FROM
[My Cube Name]
Query 5
SELECT
{ [Measures].[Measure Name] } ON COLUMNS,
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
[My Dimension Group].[Dimension Name].CurrentMember.Name = 'My String Value Carolina'
)
} ON ROWS
FROM
[My Cube Name]
Query 6
SELECT
{ [Measures].[Measure Name] } ON COLUMNS,
{
FILTER(
[My Dimension Group].[Dimension Name].[Dimension Name],
[Measures].[Measure Name] = NULL
)
} ON ROWS
FROM
[My Cube Name]
Query 7
SELECT
{ [Measures].[Measure Name] } ON COLUMNS,
NON EMPTY([My Dimension Group].[Dimension Name].[Dimension Name]) ON ROWS
FROM
[My Cube Name]
That is for now.
Conclusion
I hope someone found this article useful. Please share your valuable thoughts and comments. Your feedback is always welcomed.
Thanks in advance. Happy coding!
Kindest Regards
Sibeesh Venu
Sibeesh VenuPosted Nov 28, 2016, 7:54 AM
Santhakumar Munuswamy Thanks much for your feedback.
Santhakumar MunuswamyPosted Sep 27, 2015, 3:23 AM
Good Article
Sibeesh VenuPosted Jul 9, 2015, 4:08 AM
Vignesh Mani Thank you
Vignesh ManiPosted Jul 9, 2015, 3:42 AM
Nice one
Sibeesh VenuPosted Jul 9, 2015, 3:20 AM
Rakesh Chavda Thank you
Sibeesh VenuPosted Jul 9, 2015, 3:20 AM
Gopi Chand Thank you
Sibeesh VenuPosted Jul 9, 2015, 3:20 AM
Jaipal Reddy Thank you
RakeshPosted Jul 9, 2015, 2:51 AM
Good One
Gopi ChandPosted Jul 9, 2015, 1:14 AM
Good one
Jaipal ReddyPosted Jul 9, 2015, 12:40 AM
nice article
Sibeesh VenuPosted Jul 9, 2015, 12:32 AM
Yatendra Sharma Thank you
Sibeesh VenuPosted Jul 9, 2015, 12:32 AM
Pankaj Kumar Choudhary Thank you
Yatendra SharmaPosted Jul 9, 2015, 12:28 AM
nyc article
Pankaj Kumar ChoudharyPosted Jul 8, 2015, 8:28 PM
Nice Explain Sir..........