Converting DAX to MDX

[Justas4478]

Hi, I am trying to converte my DAX to MDX I can manage to converte simple ones but I am struggling with complex ones.
There are the measure that I cant convert and the error messages I get.

1. Days Shorted= 
VAR _Table =

    ADDCOLUMNS(

        SUMMARIZE(

        'Outbound Delivery',

        'Product Category'[Level 2],

        'Date'[Date]),

        "@Shorted", 'Outbound Delivery'[Shorted Qty])

VAR _Result =

    COUNTX(

        FILTER(

             _Table,

              [@Shorted] <>0

        ),

        [@Shorted]

    )

RETURN

  _Result
 

2. SWITCH('Outbound Delivery'[Days Shorted],BLANK(),"0 days Short",1,"1 days Short",2,"2 days Short",3,"3 days Short",4,"4 days Short","5 days Short or more")

3. CALCULATE(COUNTA('Product'[SKU Number]))

I would appreciate any help.

[Anonymous]

Hi @Justas4478 ,

Please try whether these MDXs can work:

WITH
MEMBER [Measures].[ShortedQty] AS
    [Measures].[Shorted Qty]

MEMBER [Measures].[DaysShorted] AS
    COUNT(
        FILTER(
            NONEMPTY(
                [Product Category].[Level 2].MEMBERS
                * [Date].[Date].MEMBERS,
                [Measures].[ShortedQty]
            ),
            [Measures].[ShortedQty] <> 0
        )
    )

SELECT
    [Measures].[DaysShorted] ON COLUMNS,
    NONEMPTY(
        [Date].[Date].MEMBERS
    ) ON ROWS
FROM [YourCubeName]

WITH
MEMBER [Measures].[DaysShortedLabel] AS
    CASE 
        WHEN ISEMPTY([Measures].[Days Shorted]) THEN "0 days Short"
        WHEN [Measures].[Days Shorted] = 1 THEN "1 days Short"
        WHEN [Measures].[Days Shorted] = 2 THEN "2 days Short"
        WHEN [Measures].[Days Shorted] = 3 THEN "3 days Short"
        WHEN [Measures].[Days Shorted] = 4 THEN "4 days Short"
        ELSE "5 days Short or more"
    END

SELECT
    [Measures].[DaysShortedLabel] ON COLUMNS,
    -- Add the appropriate rows or dimensions you need on ROWS here
FROM [YourCubeName]

WITH 
MEMBER [Measures].[SKUCount] AS
    COUNT(
        NONEMPTY(
            [Product].[SKU Number].[SKU Number].MEMBERS
        )
    )

SELECT 
    [Measures].[SKUCount] ON COLUMNS,
    -- Add the appropriate rows or dimensions you need on ROWS here
FROM [YourCubeName]

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Justas4478]

I just tried 3rd MDX and got this error:

[Justas4478]

I did not had time to check them all yet.
But 'Days Shorted' is just a name of DAX measure sorry for including it I undrestand why it could have caused missunderstanding.

Only this part is the actual DAX code:

VAR _Table =

    ADDCOLUMNS(

        SUMMARIZE(

        'Outbound Delivery',

        'Product Category'[Level 2],

        'Date'[Date]),

        "@Shorted", 'Outbound Delivery'[Shorted Qty])

VAR _Result =

    COUNTX(

        FILTER(

             _Table,

              [@Shorted] <>0

        ),

        [@Shorted]

    )

RETURN

  _Result

[Anonymous]

Hi @Justas4478 ,

Are you saying here:

Here I created a Calculated member called [Measures].[DaysShorted] to count the number of non-zero Shorted Qty entries.

If this doesn't work, you can try this one:

WITH 
MEMBER [Measures].[ShortedQty] AS 
    [Measures].[Shorted Qty]

SET [ShortedTable] AS 
    NONEMPTY(
        [Product Category].[Level 2].MEMBERS *
        [Date].[Date].MEMBERS,
        [Measures].[ShortedQty]
    )

MEMBER [Measures].[FilteredShortedQty] AS
    SUM(
        FILTER(
            [ShortedTable],
            [Measures].[ShortedQty] <> 0
        ),
        [Measures].[ShortedQty]
    )

MEMBER [Measures].[CountFilteredShortedQty] AS
    COUNT(
        FILTER(
            [ShortedTable],
            [Measures].[ShortedQty] <> 0
        )
    )

SELECT 
    [Measures].[CountFilteredShortedQty] ON COLUMNS,
    NONEMPTY([Date].[Date].MEMBERS) ON ROWS
FROM [YourCubeName]

But to be honest, sometimes it is difficult to convert directly from DAX to MDX. If it is still incorrect, perhaps you need to provide your data and explain what you want to accomplish with DAX/MDX.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Justas4478]

The reason why I am trying to conver DAX to MDX.
I have power bi report with these measures.
But I cant do pivot tables in power bi reports.
And since my cube is live model, I as well cant do calculated columns.
So I connected cube to excel and hoped that I can recreate those measures and use them for report creation.