title: CTE doesn't work in SSAS Cube. I want to find solution or convert it ti subquery
body: <p>I write this CTE query and the explanation is:</p>
  
  <pre><code>    WITH TP AS 
      (select  
      c.ID, c.PeriodCId, c.PeriodName, c.Status, c.StatusChangeDate, CAST(c.StartDate AS DATE) AS StartDate, c.EndDate,c.PeriodCode,
                c.PeriodType, c.ParentCId, c.MarketId, c.ParentId, c.WD, LEFT(CONVERT(varchar, c.StartDate, 112), 6) AS YEARMONTH, 
                (select count(*) from dTimePeriod c2 where c2.ParentId = c.ID and  c2.Status='actv') as #children
  from    dTimePeriod c
  where  (MarketId = 7) ),
   TP2 AS
  ( SELECT *
  FROM TP 
  WHERE  #children='12'  ),
  TP3 AS
  (SELECT TP.*, CASE WHEN (TP.WD IS NOT NULL) AND (TP.StartDate &lt;= getdate()) AND TP2.ID=TP.ParentId THEN 18 ELSE NULL END AS WorkingDays
  FROM TP LEFT JOIN TP2 ON TP2.ID=TP.ParentId)
  select * from TP3
  order by ID
  </code></pre>
  
  <p>and this is the result
  <a href="https://i.stack.imgur.com/xyjqN.png" rel="nofollow noreferrer">CTE Image</a></p>
  
  <p>I have recursive table called [dTimePeriod] this table contains different cycles and each cycle contains different number of periods,EX: one cycle has 8 periods another cycle has 12 periods and so on, I want if cycles contains 12 periods put to each period value = 18 and for others cycle periods null
  and there are some another conditions but it's not the issue.</p>
  
  <p>And when I put it in the SSAS cube doesn't work because the cube doesn't understand the CTE so I tried to find a solution but it doesn't work,
  one of them to put this CTE in a view and call this view in the cube but the view doesn't work as well.
  so I start to write it as subquery to make the cube able to understand it.
  but I am stuck, I can't write this CTE in subquery statement</p>
  
  <p>and this is the subquery where I stuck</p>
  
  <pre><code>SELECT         c.ID, c.PeriodCId, c.PeriodName, c.Status, c.StatusChangeDate, CAST(c.StartDate AS DATE) AS StartDate, c.EndDate,
                 c.PeriodCode, c.PeriodType, c.ParentCId, c.MarketId, c.ParentId, c.WD,
                 LEFT(CONVERT(varchar, c.StartDate, 112), 6) AS YEARMONTH,
                 CASE WHEN (c.WD IS NOT NULL) AND (c.StartDate &lt;= getdate()) THEN 18           
                 WHEN (c.WD IS NOT NULL) AND (c.StartDate &gt; getdate()) THEN NULL ELSE c.WD END AS WorkingDays,
  
                case when (select sub.*  from 
               (select count(*) as children  from dTimePeriod c2   where c2.ParentId = c.ID and   c2.Status='actv' ) sub ) = 12 
                then 18 else null end as WWW
  
  
  FROM           dTimePeriod c 
  WHERE         (c.MarketId = 7)
  </code></pre>