General notes
First of all, T-SQL and MDX are 2 completely different beasts. SQL is intended to query tabular relational data, while MDX is intended to query multidimensional data.
I'll start with addressing your question about common mistakes. In my opinion the most common mistake is to try and apply SQL knowledge to MDX. You really need to get into the multidimensional mindset and forget about thinking in relational structures.
For example, you don't query columns and rows but you slice dimensions and measures. See for example my answer here.
Pretty much the only thing in MDX that resembles SQL is the fact that you have a SELECT and a FROM and a WHERE clause but MDX doesn't necessarily return rows and columns but returns "axes", of which you can have more than 2.
Addressing some of your points will probably show you why SQL concepts don't map to MDX concepts:
Pivot and unpivot
You pretty much don't do this in MDX. If you look at the MDX select syntax you'll see an example like this (returning in this case a 2 dimensional result):
SELECT
[Date].[Calendar].[First8Months2003] ON COLUMNS,
[Product].[Category].Children ON ROWS
FROM
[Adventure Works]
WHERE
[Measures].[Order Quantity]
Say, you want to pivot that resultset, you just swap the axes around:
SELECT
[Product].[Category].Children ON COLUMNS,
[Date].[Calendar].[First8Months2003] ON ROWS
FROM
[Adventure Works]
WHERE
[Measures].[Order Quantity]
Bear in mind that the COLUMNS and ROWS axes are just shorthand names, you could also do:
SELECT
[Product].[Category].Children ON 0,
[Date].[Calendar].[First8Months2003] ON 1
FROM
[Adventure Works]
WHERE
[Measures].[Order Quantity]
and add a third axis and a fourth axis if you wish to do so (and if your client supports rendering that), so the result isn't a table but rather a multidimensional cellset.
Obviously, pivot and unpivot wouldn't keep up when you have more than 2 axes in your result set.
Recursive CTE's
Since MDX doesn't return "tables", there is no such thing as table expressions. You can have recursion in your calculated measures if that is something you need.
When you encounter the WITH keyword, it's defining Query-Scoped Calculated Members.
Window functions
Window functions also have no use in MDX, since they are used to calculate things that are already calculated in your cube and calculated measures. The OVER clause is pretty much the dimension members you put on your axes.
Take for example a running total. In SQL you would write something along the lines of
SELECT SUM(internet_sales) OVER (ORDER BY date) AS running_total
FROM sales;
However, in MDX you would have a calendar dimension and a sales amount measure, and your MDX would look something like:
WITH MEMBER [Measures].[Sum from start]
AS
SUM ({NULL:[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]
Conclusion
As you can see, trying to solve MDX problems by applying T-SQL principles, you will likely run into problems.
If you want to learn MDX, you need to learn MDX as if it were a completely new language and not a new syntax, because that's what it is, a different language and not a SQL dialect.
