Mondrian schema - query to get number non null and null values from a level

Question

I am quite new to mondrian and MDX. I have the following mondrian schema:

<Schema name="OFF_EDD_project" description="open food facts database">

    <!--Shared dimension for dates-->
    <Dimension type="StandardDimension" name="Date"> <!--does a degraded dimension make sense?-->
        <Hierarchy hasAll="true" allMemberName="All years"  primaryKey="date_pk">
        <Table name="DimDate"/>

            <Level name="Year"       column="year"        uniqueMembers="true"/>
            <Level name="Month"      column="month"       uniqueMembers="false"/>
            <Level name="Day"        column="day"         uniqueMembers="false"/>
        </Hierarchy>
    </Dimension>

    <Cube name="Cube1"> 
        <Table name="FactCube1"/>

            <DimensionUsage source="Date"    name="Date of creation"    foreignKey="creation_date"/>
            <DimensionUsage source="Date"    name="Date of update"      foreignKey="update_date"/>
            <Dimension name="Product" foreignKey="product">
                <Hierarchy hasAll="true" allMemberName="All products"  primaryKey="product_pk">
                <Table name="DimProduct"/>
                    <Level name="Contributor"   column="contributor"    uniqueMembers="true"/> 
                    <Level name="Nutriscore"    column="Nutriscore"     uniqueMembers="true"/>
                    <Level name="Pnns2"         column="pnss2"          uniqueMembers="false"/>
                    <Level name="Name"          column="name"           uniqueMembers="false"/>
                    <Level name="Barcode"       column="barcode"        uniqueMembers="false"/>
                </Hierarchy>
            </Dimension>    

            <Measure name="num publication per creation date"   column="creation_date"  datatype="Integer"  aggregator="count"/>
            <Measure name="num publication per update date"     column="update_date"    datatype="Integer"  aggregator="count"/>
</Schema>

I would like to run a query that returns on the rows the update years and two columns, the first with the number of products versions with a non null nutriscore and the second one with the null nutriscore.

I have tried different approaches, but nothing seems to work. Could anybody give me a suggestion on how to proceed?

Answer 1

Just uses a CASE statement within a calculated measure to differentiate between products with non-null and null Nutriscores, and it aggregates these counts by the Year level of the Date of update dimension:

WITH 
MEMBER [Measures].[NonNullNutriscore] AS 
    SUM(
        [Product].[Nutriscore].[Nutriscore].Members,
        CASE WHEN IsEmpty([Product].[Nutriscore].CurrentMember.Properties("Nutriscore")) 
        THEN 0 
        ELSE 1 
        END
    )
    
-- Calculated measure for null Nutriscore
MEMBER [Measures].[NullNutriscore] AS 
    SUM(
        [Product].[Nutriscore].[Nutriscore].Members,
        CASE WHEN IsEmpty([Product].[Nutriscore].CurrentMember.Properties("Nutriscore")) 
        THEN 1 
        ELSE 0 
        END
    )

SELECT 
{ [Measures].[NonNullNutriscore], [Measures].[NullNutriscore] } ON COLUMNS,
[Date of update].[Year].Members ON ROWS
FROM [Cube1]