How to remove null rows from MDX query results

Question

How can I remove the null row from my MDX query results?

Here is the query I'm currently working with

    select
    non empty 
    {
[Measures].[Average Trips Per Day]
,[Measures].[Calories Burned]
,[Measures].[Carbon Offset]
,[Measures].[Median Distance]
,[Measures].[Median Duration]
,[Measures].[Rider Trips]
,[Measures].[Rides Per Bike Per Day]
,[Measures].[Total Distance]
,[Measures].[Total Riders]
,[Measures].[Total Trip Duration in Minutes]
,[Measures].[Total Members]
    } on columns
    ,
    non empty 
    {
    (
    [Promotion].[Promotion Code Name].children
)
    } on rows 
from [BCycle]
where ([Program].[Program Name].&[Madison B-cycle])

;results

Kunal Khivensara Kunal Khivensara · Accepted Answer · 2018-11-28T09:44:33

This is not a null value however it is one of the children of [Promotion].[Promotion Code Name].Children.

You can exclude that particular value from children using the EXCEPT keyword of MDx.

Example query:

   //This query shows the number of orders for all products,  
   //with the exception of Components, which are not  
   //sold.  
SELECT   
   [Date].[Month of Year].Children  ON COLUMNS,  
   Except  
      ([Product].[Product Categories].[All].Children ,  
         {[Product].[Product Categories].[Components]}  
      ) ON ROWS  
FROM  
   [Adventure Works]  
WHERE  
   ([Measures].[Order Quantity])

Reference -> https://docs.microsoft.com/en-us/sql/mdx/except-mdx-function?view=sql-server-2017

How to remove null rows from MDX query results

1 Answers