cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dashboards - Dynamic content update of tiles from reports based on time

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,

 

  • we have to unpin it when a month changes, 
  • set the filter option on the report to the current month
  • repin the dashboard
  • repeat for every measure that is dependent on time

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

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

View solution in original post

Highlighted

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.

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors