topic MDX to DAX in DAX Commands and Tips

MDX to DAX

Anonymous — Wed, 20 Apr 2022 12:24:22 GMT

I am facing complexity while converting MDX to DAX(SSAS to PowerBI). Can you please help here to convert below calculations

SUM(PeriodsToDate([Time_Period].[Time_Period].[Period],[Time_Period].[Time_Period].CurrentMember),[Measures].[Hrs_Act_Admin_POS])

SUM(([Time_Period].[Time_Period].currentmember.parent.firstsibling.firstchild:[Time_Period].[Time_Period].currentmember.parent.lastsibling.lastchild),
[Measures].[Hrs_LY_Admin_POS])

SUM(([Time_Period].[Time_Period].currentmember.FirstSibling:[Time_Period].[Time_Period].currentmember.lastsibling),[Measures].[Hrs_LY_POS])

Re: MDX to DAX

amitchandak — Wed, 20 Apr 2022 16:16:47 GMT

Anonymous , Can you tell us what these are doing , might be easy to suggest

also, check

https://www.sqlbi.com/articles/converting-mdx-to-dax-first-steps/

Re: MDX to DAX

Anonymous — Thu, 21 Apr 2022 02:03:56 GMT

HI Amith,

These are calculated mesures in my SSAS multildimensinal model. We are planning to migrate the same to PowerBI reports.

Re: MDX to DAX

amitchandak — Thu, 21 Apr 2022 04:20:37 GMT

Anonymous , I am not an expert at MDX, so wanted what you need, so I can help.

Like we have functions datesmtd, Datesqtd, and datesytd for till date.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

But we can combine them with slicer selection

Switch Period =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType])
"YTD",if( Month(_max) <4 , date(year(_max)-1,4,1) ,date(year(_max),4,1)), //FY April -March
"QTD",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1,
"MTD",eomonth(_max,-1)+1 ,
BLANK())
return
CALCULATE(Sum(Sales[Sales[Amount]),DATESBETWEEN('Date'[Date],_min,_max))

Re: MDX to DAX

Anonymous — Thu, 21 Apr 2022 05:10:36 GMT

Hi Amit,

Thank you so much for your response. Please find below details of mdx query details.

Measure = SUM(PeriodsToDate([Time_Period].[Time_Period].[Period],[Time_Period].[Time_Period].CurrentMember),[Measures].[Hrs_Act_Admin_POS])

Above MDX query is to create calculated measure for belwo purpose.

to navigate between same level members. It returns the set of sibling members from First Sibling to the specified member. For example, If you want to list the members from one position to a particular member, then we can use this MDX PeriodsToDate function.

for more information about PeriodToDate https://www.tutorialgateway.org/mdx-periodstodate-function/

Re: MDX to DAX

Anonymous — Fri, 22 Apr 2022 07:02:16 GMT

amitchandak

Please let me know if there is any suggesions.

Re: MDX to DAX

Anonymous — Tue, 26 Apr 2022 09:46:46 GMT

Hi Anonymous ,

In order to be able to provide you with a suitable solution more quickly, could you please provide some simplified sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards