Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Justas4478
Post Prodigy
Post Prodigy

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
Justas4478_1-1731334416523.png 

 

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]))

Justas4478_0-1731334185328.png


I would appreciate any help.

 

5 REPLIES 5
Anonymous
Not applicable

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.

I just tried 3rd MDX and got this error:

Justas4478_0-1731401942308.png

 

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
Not applicable

Hi @Justas4478 ,

Are you saying here:

vjunyantmsft_0-1731403204510.png

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.

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.