请求协助：为MDX度量值'Estimated Unbilled Outstanding Subtotal £'添加筛选

Hey Adrian, no worries—let's get that filter added to your query. Depending on what kind of condition you want to apply, we can use either the WHERE clause or the FILTER function. Here's how to adapt both to your existing MDX:

1. Using the WHERE Clause (Global Dimension Filters) #

If you need to filter your entire query based on a dimension member (like a specific date range, region, or another attribute), the WHERE clause is the cleanest approach. It applies globally to all rows and columns.

For example, if you wanted to only include data from March 2024, you'd add the WHERE clause at the end of your query:

SELECT 
  { [Measures].[Estimated Unbilled Outstanding Subtotal £] } ON COLUMNS,
  { 
    NONEMPTY(
      { [Customer].[Billing Team].[All].CHILDREN } * 
      { [Customer].[Customer Name].[All].CHILDREN } * 
      { [Account].[Account ID].[All].CHILDREN } * 
      { [Account].[Account Type].&[Import], [Account].[Account Type].&[Other Non Billable] },
      { [Measures].[Estimated Unbilled Outstanding Subtotal £] }
    ) 
  } ON ROWS
FROM [BDW]
WHERE ([Date].[Month].&[2024-03]); -- Replace with your target dimension member

This will restrict the entire result set to only include data matching the WHERE condition.

2. Using the FILTER Function (Measure Value Conditions) #

If you want to filter rows based on the value of your target measure itself (e.g., only show rows where the subtotal is greater than £0), wrap your row set in the FILTER function. This lets you set specific numeric conditions for the measure.

Here's how to modify your query to only include rows where the subtotal is greater than 0:

SELECT 
  { [Measures].[Estimated Unbilled Outstanding Subtotal £] } ON COLUMNS,
  { 
    FILTER(
      NONEMPTY(
        { [Customer].[Billing Team].[All].CHILDREN } * 
        { [Customer].[Customer Name].[All].CHILDREN } * 
        { [Account].[Account ID].[All].CHILDREN } * 
        { [Account].[Account Type].&[Import], [Account].[Account Type].&[Other Non Billable] },
        { [Measures].[Estimated Unbilled Outstanding Subtotal £] }
      ),
      [Measures].[Estimated Unbilled Outstanding Subtotal £] > 0 -- Your measure condition here
    ) 
  } ON ROWS
FROM [BDW];

Note: The NONEMPTY function already removes rows where the measure is null, but FILTER lets you add more precise conditions (like greater than a specific value, between two numbers, etc.).

Quick Tip #

• Use WHERE for filtering on dimension attributes (e.g., dates, customer segments).
• Use FILTER when you need to filter directly on the measure's numeric value.

Let me know if you need help adapting this to your specific filter condition!

内容的提问来源于stack exchange，提问作者Salty