Fact Level MDX Filtering

Question

im pretty new to mintx sintaxys.

I have the following requirement so that I can solve it using mdx, if possible. I need to show the number of SALE transactions whose amout value is greater than "X", the number of SALE transactions whose size is less than "Y", the number of CREDIT transactions whose amout value is greater than "Z". etc. my cube has a measure called "sum" with the total function "sum" and the transaction number with the function "count" agregate and time coefficient, transaction size and others.

The fact is that X, Y and Z are dynamic values ​​and are configured by users, I need to read these values, build a query and execute it vía xmla.

I use the result set as the next

                  Greater than > 200 USD       less than < 0.10       total

          SALE            150                         10               300
          CREDIT          200                         30               600
          VODI            10                           2                60

any help you can provide me, I will be grateful

Answer 1

This is only possible if you have an attribute that was at the transaction level, otherwise your measures will be pre-aggregated to a higher level.

If you have something like the [Transaction ID] attribute, you can write a query as shown below.

WITH 
  MEMBER Measures.[Greater than 200 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.[Less than 0.10 USD] as 
    SUM(Filter([Transaction ID].[Transaction ID].[Transaction ID], Measures.Amount > 200)
       , Measures.Count)
  MEMBER Measures.Total as Measures.Count
SELECT
  {Measures.[Greater than 200 USD]
    ,Measures.[Less than 0.10 USD]
    ,Measures.[Total]} ON columns
 , [Transaction Type].[Transaction Type].[Transaction Type] ON Rows
FROM <Cube>