cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wwcnd Member
Member

Show items with no data in selected date period

I have some charts where there is no data for all the months, but i still want to have these months shown, e.g last 12 months when i select show items with no data and add a data filter to show the last 15 weeks it will still take all the dates from the calendar tabldata as is.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Seward12533 New Contributor
New Contributor

Re: Show items with no data in selected date period

Hi, you need to force a zero to be displayed instead of null if no results and then limit what gets displayed so you don't show all values in your date table/range.  Lots of ways to do this. 

 

Here is what I do to force zero results. 

 

Result with Zero = VAR result = [Measure or Calc] RETURN 
IF(result,result,0)

You can then use the Visual Filters to limit to last 12 Months, 12, Weeks or whatever or use some other blocking function to only show this result if there data in anotehr measure. 

 

See this example I built for someone else that domonstrates this principle in this case I'm using a visual filter to only display resultls where there is actual data. 

 

https://1drv.ms/u/s!AuCIkLeqFmlhhJh3pj3q9XzrxpxAng

 

View solution in original post

Seward12533 New Contributor
New Contributor

Re: Show items with no data in selected date period

Can you share example? Do you have a date table?

 

Once you have your graph showing all time when you select the all values even with zero.  Filter the visual based on relative dates (see snip below)

 

snip_20180815102744.png

View solution in original post

8 REPLIES 8
Seward12533 New Contributor
New Contributor

Re: Show items with no data in selected date period

Hi, you need to force a zero to be displayed instead of null if no results and then limit what gets displayed so you don't show all values in your date table/range.  Lots of ways to do this. 

 

Here is what I do to force zero results. 

 

Result with Zero = VAR result = [Measure or Calc] RETURN 
IF(result,result,0)

You can then use the Visual Filters to limit to last 12 Months, 12, Weeks or whatever or use some other blocking function to only show this result if there data in anotehr measure. 

 

See this example I built for someone else that domonstrates this principle in this case I'm using a visual filter to only display resultls where there is actual data. 

 

https://1drv.ms/u/s!AuCIkLeqFmlhhJh3pj3q9XzrxpxAng

 

View solution in original post

Community Support Team
Community Support Team

Re: Show items with no data in selected date period

@wwcnd,

 

As mentioned above, visual level filter is more credible.

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

Re: Show items with no data in selected date period

i will try and test it tomorrow and get back, thanks
Highlighted
wwcnd Member
Member

Re: Show items with no data in selected date period

i cannot get it to work sorry 

Seward12533 New Contributor
New Contributor

Re: Show items with no data in selected date period

Can you share example? Do you have a date table?

 

Once you have your graph showing all time when you select the all values even with zero.  Filter the visual based on relative dates (see snip below)

 

snip_20180815102744.png

View solution in original post

wwcnd Member
Member

Re: Show items with no data in selected date period

looks like its working now 🙂
DandyDan Frequent Visitor
Frequent Visitor

Re: Show items with no data in selected date period

Yes, my filter worked when I only used Visual Level Filter as suggested vs. adding an end-user filter onto the canvas.  This does seem like a bug in the sense that Power BIs (data element) dropdown option of "Show Items with No Data" doesn't appear to work. 

 

Similar to the initial question, my issue was in regards to a date field.  Perhaps the "Show Items with No Data" option works for non-date data types?  

TuanManh Visitor
Visitor

Re: Show items with no data in selected date period

This is what worked for me if you want to use date hierarchy.

Num of something =
VAR min_date = CALCULATE(MIN('Mews_Table'[Date]);ALLSELECTED('Mews_Table'))
VAR x = COUNT(Mews_Table[Item]) )
RETURN
IF( AND(ISBLANK(x); MIN(Mews_Table[Date].[Date]) >= min_date; MAX('Mews_Table'[Date].[Date]) <= TODAY()); 0; x )
 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors