Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago

Converting DAX to MDX

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.

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Justas4478
      Post Prodigy
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.