A simple example filtering all cities containing an "ar" string
Query
Filter( [Geography].[City].members as s, PatternMatches( ".*ar.*", s.currentmember.name ) )
Result
{
[Geography].[Geo].[City].[Caracas],
[Geography].[Geo].[City].[Paris],
[Geography].[Geo].[City].[Barcelona]
}
Assertion : MDX Equals
{[Geography].[City].[Caracas],[Geography].[City].[Paris],[Geography].[City].[Barcelona]}
Assertion : MDX Equals
Filter( [Geography].[City].members as s, s.currentmember.name.contains("ar") )
A simple example filtering all cities containing an "ar" string
Query
Filter( [Geography].[City].members as s, s.currentmember.name.contains("ar") )
Result
{
[Geography].[Geo].[City].[Caracas],
[Geography].[Geo].[City].[Paris],
[Geography].[Geo].[City].[Barcelona]
}
Assertion : MDX Equals
{[Geography].[City].[Caracas],[Geography].[City].[Paris],[Geography].[City].[Barcelona]}
Assertion : MDX Equals
Filter( [Geography].[City].members as s, PatternMatches( ".*ar.*", s.currentmember.name ) )
A simple example checking if a number isNan (is not a number)
Query
WITH
MEMBER [Measures].[isNan] AS isNaN( 0.0 / 0.0 )
SELECT
{[Measures].[isNan]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[isNan] AS true
SELECT
{[Measures].[isNan]} ON 0
FROM
[Sales]
A simple example checking if a number is infinite
Query
WITH
MEMBER [Measures].[isNan] AS IsInfinity( 1.0 / 0.0 )
SELECT
{[Measures].[isNan]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[isNan] AS true
SELECT
{[Measures].[isNan]} ON 0
FROM
[Sales]
Returns the current date (without time information)
Query
Result
2022-06-08
Assertion : MDX Equals
An example to convert a date or datetime to a string using a specific format
Query
WITH
MEMBER [Measures].[isDate] AS DateToString( StringToDate("4/6/2001","d/M/yyyy"), "d MMMM yyyy" )
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS "4 June 2001"
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
An example to convert a date or datetime to a string using a specific format and a locale
Query
WITH
MEMBER [Measures].[isDate] AS DateToString( StringToDate("4/6/2001","d/M/yyyy"), "d MMMM yyyy", "fr" )
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS "4 juin 2001"
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
An example to convert a String to a date using a specific format
Query
WITH
MEMBER [Measures].[isDate] AS StringToDate("4/6/2001","d/M/yyyy")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS StringToDate("6/4/2001","M/d/yyyy")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
An example to convert a String to a date using a specific format
Query
WITH
MEMBER [Measures].[isDate] AS StringToDate("4 Juin 2001","d MMMM yyyy", "fr")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS StringToDate("6/4/2001","M/d/yyyy")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
An example to convert a String to a datetime using a specific format
Query
WITH
MEMBER [Measures].[isDate] AS StringToDateTime("4/6/2001 12","d/M/yyyy hh")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
isDate |
2001-06-04T00:00:00.000 |
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS StringToDateTime("6/4/2001:12","M/d/yyyy:hh")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
An example to convert a String to a datetime using a specific format and a locale.
Query
WITH
MEMBER [Measures].[isDate] AS StringToDateTime("4 Juin 2001 12","d MMMM yyyy hh", "fr")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
isDate |
2001-06-04T00:00:00.000 |
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS StringToDateTime("6/4/2001:12","M/d/yyyy:hh")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
An example how to build a date
Query
WITH
MEMBER [Measures].[isDate] AS DateTime(2001,6,4)
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS StringToDate("4/6/2001","d/M/yyyy")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
An example how to build a datetime
Query
WITH
MEMBER [Measures].[isDate] AS DateTime(2001,6,4,0,0)
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Result
isDate |
2001-06-04T00:00:00.000 |
Assertion : MDX Equals
WITH
MEMBER [Measures].[isDate] AS StringToDateTime("4/6/2001","d/M/yyyy")
SELECT
{[Measures].[isDate]} ON 0
FROM
[Sales]
Converts an RGB color into an Hexadecimal one.
Query
WITH
MEMBER [Measures].[Hexa] AS ToHexColor( RGB(16,17,18) )
SELECT
{[Measures].[Hexa]} ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[Hexa] AS "#101112"
SELECT
{[Measures].[Hexa]} ON 0
FROM
[Sales]