Multi where conditions in an mdx query (LINQ to mdx) by Percolator analysis services

Question

am trying to apply multiple where conditions on the below linq query :

            using (var _DB = new MyDB())
        {
                   var QryYear = from model in _DB.DL
                          where model.BusinessDate.BusinessDate.Years["2016"]
                          where model.BusinessDate.BusinessDate.Years["2017"]
                          where model.NumL.Num[FirstNumber].Function("Lag(2)") | model.NumL.Num[SecoundNumber]
                          select new MdxQuery
                          { OnColumns = model.Amount };
            result = QryYear.Percolate<AmountModel>().ToList();

but am getting this exception ({"Query (13, 1) The BusinessDate hierarchy is used more than once in the Crossjoin function."}) this is how the query looks like in mdx:

SELECT{Measures.[Amount]} ON 0
 //----------             From / SubCube               ----------
 FROM [DL]
//----------                 Slicers                  ----------
WHERE
([BusinessDate].[BusinessDate].[Years].[2016],  [BusinessDate].[BusinessDate]. 
[Years].[2017]
,   ([DL].[Num].[55].Lag(2) : [DL].[Num].[2305]))} 

Percolator analysis services github

Answer 1

Braces () in MDX either signify a tuple or crossjoin, you are using braces in the WHERE clause. Try making the two specified years into a set by putting curly braces around them:

SELECT{Measures.[Amount]} ON 0
 //----------             From / SubCube               ----------
 FROM [DL]
//----------                 Slicers                  ----------
WHERE
(
{[BusinessDate].[BusinessDate].[Years].[2016],  
[BusinessDate].[BusinessDate]. [Years].[2017]
}
,   ([DL].[Num].[55].Lag(2) : [DL].[Num].[2305]))

---

note

Just to confirm. The following MDX is valid:

SELECT 
    NON EMPTY [Measures].[Internet Sales Amount] ON 0,
    NON EMPTY [Date].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works];

It returns this:

But if we move 2 year members to the WHERE clause, like your script, then it is not valid MDX:

SELECT 
    NON EMPTY [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE ([Date].[Calendar Year].&[2005], [Date].[Calendar Year].&[2006]);

Exception message:

Executing the query ... Query (4, 1) The Calendar Year hierarchy is used more than once in the Crossjoin function. Run complete

But if you follow my suggestion and add the curly braces then it is valid:

SELECT 
    NON EMPTY [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE ( {[Date].[Calendar Year].&[2005], [Date].[Calendar Year].&[2006]});

Results in