How to include null values in filter visualization?

[ajames95]

Hi, 

I have a simple graph that i am filtering with a date slicer. 

However I want the null values to show when i click on certain dates? 

This is when I there is no filtering on the graph. 

When I select a specific month, I want all the type of category on the x axis, even if theres no data to show this. 

Is there a way we can do this on Power BI?

[v-juanli-msft · Accepted solution]

Hi @ajames95 

Leave main data table and calendar table no relationship

Create a measure

Measure =
VAR slectedmo =
    SELECTEDVALUE ( 'calendar'[year-month] )
VAR y_m =
    FORMAT ( MAX ( 'Table'[date] ), "yyyy-mm" )
RETURN
    CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', slectedmo = y_m ) )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ajames95]

Hi Maggie, 

I only have one table - 

1 Year/Month = FORMAT('Table'[Logged Date],"YYYY-MM")

[v-juanli-msft]

Hi @ajames95 

There is no direct way to slove your problem, so i provide a workaround.

Please create a calendar table:

New Table-> enter this formula

calendar = ADDCOLUMNS(CALENDARAUTO(),"year-month",FORMAT([Date],"yyyy-mm"))

Then follow my methods in the previous post.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

Hello @ajames95 

Under Axis on the category line pick the down arrow and select 'Show items with no data"

[ajames95]

Hello @jdbuchanan71 

Thanks for that but it did not work.. 

[jdbuchanan71]

Can you share your .pbix file?