topic Re: How to show the next month which doesn't have records in fact table? in Developer

How to show the next month which doesn't have records in fact table?

Priya_E — Fri, 01 Sep 2023 10:14:58 GMT

Hello All,

The following Grid shows Count of service metric @ Month level.

For Service A, the service has been stopped after 2023 March. So User wants to display the month at which the service has stopped as highlighted below.

How can we achieve this?

Thanks all for your help.

Re: How to show the next month which doesn't have records in fact table?

DataNinja777 — Sat, 02 Sep 2023 01:23:14 GMT

Hi @Priya_E

In order to show the blank values, I created dimension tables for time dimension (i.e., calendar table) and service type dimension, and created relationships with the fact table.

Then, right click those dimension fields in the visualization pane, and select "Show items with no data" for both fields in the visualization taken from the calendar table and service type dimension table.

This will produce the visualization which you requested.

Re: How to show the next month which doesn't have records in fact table?

Priya_E — Sat, 02 Sep 2023 07:19:02 GMT

Thanks your solution works by bringing all the other Months in the dimension table.But in my case I need to show just one month instead of all months. How do we restrict to show only one month (highlighted)after the data has stopped receiving.

I am trying to play around with Rank. Any help?

Re: How to show the next month which doesn't have records in fact table?

DataNinja777 — Sat, 02 Sep 2023 16:06:36 GMT

If the service types are limited and static like in this case, creating a dax table expression like below can produce the output you require, but if there are many service types, writing out the formula in this way will be time consuming and not feasible to maintain it manually. There probably is a smarter way to do this dynamically respecting the change in the service dimension table, but I am not sure how to do that.

I attach the pbix file for the above interim solution.

Show item with no value.pbix