"Show items with no data" ineffective :-(

[F_Reh]

Good Afternoon,

I would be greatly appreciate some Tips on the following issue I have.

Background:

I have a couple of Visuals:

The X-Axis can be toggled based on the Toggle buttons Slicer (sitting above it), which is based on a field parameter:

FieldParameter_XAxis = {

    ("Month_Year", NAMEOF('<_Table_Name_>'[Year_Month]), 0),

    ("<**********>", NAMEOF('<_Table_Name_>'[ZZZZZZZZZZ]), 1)

}

The Y-Axis is plainly the sum of a Numerical-based field within the same Table '<_Table_Name_>', and will be the only field used for the Y-Axis, regardless of which X-Axis unit is selected (from the Toggle buttons):

<Measure_Y_Axis> = SUM('<_Table_Name_>'[##########])

Problem:

In the above example, there is no data for the month of July, but unfortunately applying "Show items with no data" has no effect in bringing July to appear on the X-Axis.

Research Done:

I checked the following page: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data. However, I cannot identify which of the plausible causes, would be applicable since all fields used from both X-axis and Y-Axis are coming from the very same table (Table '<_Table_Name_>').

"The Show items with no data feature doesn't have any effect in the following circumstances:

• There's no measure added to the visual, and the grouping columns come from the same table.
• Groups are unrelated. Power BI doesn't run queries for visuals that have unrelated groups.
• The measure is unrelated to any of the groups. In this case, the measure will never be blank for only some group combinations.
• There's a user-defined measure filter that excludes blank measures. For example: SalesAmount > 0"

Kind Regards

[F_Reh]

It seems to be working now. July now appears in the X-Axis !

"Show items with no data" needed to be re-checked because I had removed the the previous version of the Field Parameter, modified it and brought it back in (which off course would have reset "Show items with no data" to be unchecked again).

[F_Reh]

It seems to be working now, and July is visible on the X-Axis !

"Show items with no data"  needed to be checked again (because after making the field Parameter modifications I removed it and brought it back again, so obviously "Show items with no data" was no longer checked).

[F_Reh]

@Daniel29195  Thanks. It was indeed the case in this instance that there was no July data.

But there is another complication with the Table, it is unable to join with the DIM Date Table because it does not contain Daily dates, which leads to a Many-to-Many Cardinality issue. It simply contains one date field denoting which month it pertains to.

Kind Regards

[Daniel29195]

@F_Reh 

you can link dimdate[date] to the column that you have in the your table year_month_date

this will be a 1-many relationship . 

dimdate                                                your_table  date                     values ... .      

2023-01-01                                       2023-01-01                                     20

2023-01-02                                         2023-01-01                                    30

2023-01-03                                        2023-01-01                                     40

.....                                                       2023-02-01                                     40

----                                                      2023-03-01                                     50

if you use date or month from dimdate inm a visual, you will get the following : 

2023-01-01         90 

2023-02-01          40

2023-03-01            50 

if you use the month insteda of date : 

january :        90 

feb                 40

march            50

or do you have some concerns ? 

[Daniel29195]

@F_Reh 

you should create w dimdate table the proper way (https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query)

then link this dimdate to your table . 

and read the date from the dimdate,

the reason july is not showing, is that you dont have it in the your table. 

however in the dimdate you will have july. 

so it will appear,  and the sum(values) =  blank() . 

let me know if this works for you . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[F_Reh]

1. I created the Dim Table ('<Dim_Table>').

2. Linked the Fact Table column ('<_Table_Name_>'[Year_Month]) to the new Dim Table column '<Dim_Table>'[YearMonth].

3. I changed the Field Parameter so that the Slicer now uses '<Dim_Table>'[YearMonth]:

From 

FieldParameter_XAxis = {

    ("Month_Year", NAMEOF('<_Table_Name_>'[Year_Month]), 0),

    ("<**********>", NAMEOF('<_Table_Name_>'[ZZZZZZZZZZ]), 1)

}

To,

FieldParameter_XAxis = {

    ("YearMonth", NAMEOF('<Dim_Table>'[YearMonth]), 0),

    ("<**********>", NAMEOF('<_Table_Name_>'[ZZZZZZZZZZ]), 1)

}

Unfortunately still getting the same result in that there is still no spot for the month of July: