The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am facing complexity while converting MDX to DAX(SSAS to PowerBI). Can you please help here to convert below calculations
1.
SUM(PeriodsToDate([Time_Period].[Time_Period].[Period],[Time_Period].[Time_Period].CurrentMember),[Measures].[Hrs_Act_Admin_POS])
2.
SUM(([Time_Period].[Time_Period].currentmember.parent.firstsibling.firstchild:[Time_Period].[Time_Period].currentmember.parent.lastsibling.lastchild),
[Measures].[Hrs_LY_Admin_POS])
3.
SUM(([Time_Period].[Time_Period].currentmember.FirstSibling:[Time_Period].[Time_Period].currentmember.lastsibling),[Measures].[Hrs_LY_POS])
@MMadhusudhan , 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/
HI Amith,
These are calculated mesures in my SSAS multildimensinal model. We are planning to migrate the same to PowerBI reports.
@MMadhusudhan , 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))
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/
Hi @MMadhusudhan ,
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
56 | |
31 | |
30 | |
24 | |
21 |