Forum Discussion

ipezakas's avatar
ipezakas
Icon for Helper I rankHelper I
8 months ago
Solved

MDX to DAX

Hello community,

 

I am posting here after a long try to find by my self how to replicate some MDX measures coming from a mutidimensional cube to power bi DAX measures.

The whole project scope is to migrate mutidimensional cubes from on premise server to Fabric Lakehouse. Part of that, is to re create DAX measures that give the same results of the MDX calculations of the cube.
Some info that may help:
Calendar table is connected on the fact table on Date Key.
Calendar table has Time hierachy : 1)Year 2)Semester 3)Quarter 4)Month 5)Date

Payments Amount = SUM('Fact Table[EURAMT])

Payments Count = COUNTROWS(Fact Table)

 

MDX Calculation :
1)Payments QTD =

CASE WHEN [Calendar].[Time].CurrentMember.level is [Calendar].[Time].[Month ] THEN           ((ParallelPeriod([Calendar].[Time].[Quarter ], 1, [Calendar].[Time].currentmember)).parent.lastchild , [Measures].[Payments Amount])             ELSE NULL END

2)Payments Count QTD = 

CASE WHEN [Calendar].[Time].CurrentMember.level is [Calendar].[Time].[Month ] THEN           ((ParallelPeriod([Calendar].[Time].[Quarter ], 1, [Calendar].[Time].currentmember)).parent.lastchild , [Measures].[Payments Count])             ELSE NULL END

 

This is the result from the cube by setting Transaction Status as dimension apart from Calendar and filters for Jan, Feb, Mar 2024.

YearSemesterMonthTransaction StatusPayments Count QTD Payments QTD

2024

Q1Jan2024Failed59596711345607539.19
2024Q1Jan2024Succeded60068692495260633.5
2024Q1Feb2024Failed59596711345607539.19
2024Q1Feb2024Succeded60068692495260633.5
2024Q1Mar2024Failed59596711345607539.19
2024Q1Mar2024Succeded60068692495260633.5

 

This post needs more information, posting asap

  • ipezakas's avatar
    ipezakas
    8 months ago
    lbendlin cengizhanarslan thank you setting my mind free, i created the following measure which works exactly as i wanted.

    Payments QTD =
    VAR mkey = SELECTEDVALUE ( 'Calendar'[MonthKey] )        
    VAR y    = QUOTIENT ( mkey, 100 )                      
    VAR m    = MOD ( mkey, 100 )                            
    VAR q    = ROUNDUP ( DIVIDE ( m, 3 ), 0 )                
    VAR prevY = IF ( q = 1, y - 1, y )                      
    VAR prevQ = IF ( q = 1, 4, q - 1 )                      
    VAR lastMonthOfPrevQ = prevQ * 3                       
    VAR anchorMonthKey   = prevY * 100 + lastMonthOfPrevQ    
    RETURN
    IF (
        NOT ISBLANK ( mkey ),                               
        CALCULATE (
            [Payments Amount],
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[MonthKey] = anchorMonthKey )
        )
    )

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    8 months ago
    how to replicate some MDX measures coming from a mutidimensional cube to power bi DAX measures.

    Don't.  Formulate the business problem and then implement the DAX solution based on that. Forget that MDX was ever involved.

     

    Use SUMMARIZECOLUMNS and ISINSCOPE

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    8 months ago

    Instead of making it too complex why dont you simply use time-intelligence functions to calculate those values? It seems that it could be as simple as writing "TOTALQTD([Payments Amount], 'Calendar'[Date])". But do not forget to assign a date table first.

  • ipezakas's avatar
    ipezakas
    Icon for Helper I rankHelper I
    8 months ago
    lbendlin cengizhanarslan thank you setting my mind free, i created the following measure which works exactly as i wanted.

    Payments QTD =
    VAR mkey = SELECTEDVALUE ( 'Calendar'[MonthKey] )        
    VAR y    = QUOTIENT ( mkey, 100 )                      
    VAR m    = MOD ( mkey, 100 )                            
    VAR q    = ROUNDUP ( DIVIDE ( m, 3 ), 0 )                
    VAR prevY = IF ( q = 1, y - 1, y )                      
    VAR prevQ = IF ( q = 1, 4, q - 1 )                      
    VAR lastMonthOfPrevQ = prevQ * 3                       
    VAR anchorMonthKey   = prevY * 100 + lastMonthOfPrevQ    
    RETURN
    IF (
        NOT ISBLANK ( mkey ),                               
        CALCULATE (
            [Payments Amount],
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[MonthKey] = anchorMonthKey )
        )
    )
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      8 months ago

      Good. Now throw this code away and reimplement using window functions.