Get rid of blank small multiples

[Anonymous]

Hello,

I have a stacked column chart where the x-axis represents a calculated column called LAG WEEK, which includes weeks 1 to 14. Week 14 is renamed to ACT. I am using this field on the x-axis and the Fiscal Qtr field in small multiples. For the quarters 2024Q4 and 2025Q3, all weeks are displayed with their values. However, for 2025Q4, since we are currently in week 9 as per my data , weeks 10 to 14 are shown as blank spaces. I would like to remove these blank spaces from the chart. My table name is Query3, and the value is a measure named value2. How can I fix this?  show items with no data is unchecked for me.. Tried few measures for hiding it did not work..

Thank you.

i want chart to look like this 

[v-pnaroju-msft]

Hi Ohboy_991,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi Ohboy_991,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi Ohboy_991,

Thank you for your follow-up.

Based on my understanding, even with the Show items with no data option unchecked, Power BI may still reserve space on the axis when using Small Multiples with a categorical x-axis. This occurs because the axis includes all available values in the column (for example: LAG WEEK 1–14), and Small Multiples do not automatically adjust to include only the data that exists for each individual quarter.

Kindly follow the workaround approach below, which might help resolve the issue:

1. Create a calculated table to include only the rows with existing data:
   FilteredQuery3 =
   FILTER (
   Query3,
   NOT ISBLANK(Query3[value2])
   )

2. Use FilteredQuery3 for your visual instead of Query3.

3. On the X-axis, employ a modified version of your LAG WEEK column to rename Week 14 to "ACT" only if data exists:
   DisplayWeek =
   IF(
   Query3[LAG WEEK] = 14 && ISBLANK(Query3[value2]),
   BLANK(),
   IF(Query3[LAG WEEK] = 14, "ACT", Query3[LAG WEEK])
   )

4. Place this DisplayWeek on the X-axis, and your value2 measure as the Values.

5. Ensure that the X-axis is set to Categorical (not Continuous) and that the Show items with no data option is unchecked.

   Additionally, please refer to the following link for more information:

   Create small multiples in Power BI - Power BI | Microsoft Learn
   
   

   If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

   Thank you.

[Anonymous]

@freginier  @v-pnaroju-msft  , Unfortunately, the above solutions did not work

[v-pnaroju-msft]

Hi Ohboy_991,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi Ohboy_991,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Thank you, @freginier, for your response.

Hi @Anonymous,

We would like to check if the solution provided by @freginier  has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[freginier]

Hey there!

You can modify the axis of the chart to exclude the weeks with no data. To do this:

• Step 1: Go to the Visualizations pane.

• Step 2: Select the x-axis and ensure that the "Show items with no data" option is unchecked.

• Step 3: If you have weeks with no data (like weeks 10–14 for 2025Q4), ensure that you filter the data in such a way that these weeks are not displayed as empty spaces.

You can also try to create a DAX measure that filters out the weeks with no data and use this measure for the chart.

Here’s an example measure to achieve that:

ValueWithoutBlankWeeks =
IF(
NOT(ISBLANK(SUM(Query3[value2]))),
SUM(Query3[value2]),
BLANK()
)

This measure will return a blank for weeks with no data and display the value when data exists.

Also since your calculated column (LAG WEEK) includes all weeks (1-14), ensure that the weeks with no actual data are being filtered out in your data model. You can create a calculated table that excludes the blank weeks from your original data.

Example DAX for excluding blank weeks:

FilteredWeeks =
FILTER(
Query3,
NOT(ISBLANK(Query3[value2]))
)

If all else fails, if the blank spaces are part of the "Small Multiples" feature, go to the "Format" pane and disable Small Multiples for the axis with blank spaces.

Hope this helps!

😁😁