Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
We have our semantic layer in a SSAS multi-dimensional cube with Power BI reports connected in DirectQuery mode. The challenge we have is with pinning report elements based on time aggregations such as Month-to-date. Since there is no "time" concept on a dashboard, when a tile based on MTD time aggregation is pinned to a dashboard,
The challenge is magnified when we have measures that are based on daily time aggregation such as sales by hour. These tiles need to be unpinned and repinned every day. This is not feasible.
The only workaround is to make the MDX time aggregation measures "insensitive" to time and default them to the current date. This does not seem like a good solution?
Has anyone come across this challenge and can provide help on overcoming it?
Many thanks.
Andrew
Solved! Go to Solution.
Hi @mistophiles,
In a SQLS erver analysis services database cube, we can caluclate MTD and YTD calculation. And in your scenario, you connect to this cube in DirectQuery mode, so calculation is not unavailable in report lelve. You need to do this in cube by using today() function. And it seems that you had done this alreadly. Here are some links for you reference.
Microsoft Analysis Services Cube: MDX Calculation for YTD,MTD and Today()
MDX Calculated Member Spanning Multiple Date Dimensions
Regards,
Charlie Liao
Hi @mistophiles,
In a SQLS erver analysis services database cube, we can caluclate MTD and YTD calculation. And in your scenario, you connect to this cube in DirectQuery mode, so calculation is not unavailable in report lelve. You need to do this in cube by using today() function. And it seems that you had done this alreadly. Here are some links for you reference.
Microsoft Analysis Services Cube: MDX Calculation for YTD,MTD and Today()
MDX Calculated Member Spanning Multiple Date Dimensions
Regards,
Charlie Liao
Thank you, Charlie. Yes, we have solved the problem using a similar approach to the what you suggested. Power BI Dashboards need some attention from the Product team as they lack the ability to use time making it cumbersome for some large enterprise customers.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.