CODEWITHSUNDEEP
sqldatabasemdx
Valentin
Valentin

Reputation: 61

Different filters for rows and columns in MDX

I have the following MDX query

SELECT 
{[Year].[2020],[Year].[2019],[Year].[2020]} on 0,

{[Sales].[GER],[Position].[EU],[Position].[US],[Position].[BL]} on 1  

FROM  
[DB]    

WHERE ([Period].[FULL],[Content].[ALL],[CUR].[EUR])

Returning this table:

          YEAR   2020 (€) 2019 (€) 2020 (€) 
Position
GER                 
EU
US
BL

However, for each row and column I want to apply a fiter. For columns, I want the currency to change and for rows I want the Period to change.

My table should look therefore like this:

          YEAR   2020 ($) 2019 (€) 2020 (€) 
Position
GER (YE)                
EU (YB)
US (YE)
BL (YB)

I tried using subselect and filter but it did not work.

Any ideas?

Upvotes: 1

Views: 191

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

The rows and columns axis are defined by sets. A set has tuples. A tuple has members. So we created a columns axis with a set of 3 tuples. Each tuple specifies two members: the year and the currency. That’s how to apply a different filter to each column.


SELECT 
{
([Year].[2020] ,[CUR].[USD]),
([Year].[2019] ,[CUR].[EUR]),
([Year].[2020] ,[CUR].[EUR])
} on 0,

{[Sales].[GER],[Position].[EU],[Position].[US],[Position].[BL]} on 1  

FROM  
[DB]    

WHERE ([Period].[FULL],[Content].[ALL])

Upvotes: 1

Related Questions