Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 tabl
Solved! Go to Solution.
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
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)
This worked for me thanks.
I had to use && instead of the AND( though.
@Anonymous,
As mentioned above, visual level filter is more credible.
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?
i cannot get it to work sorry
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)
This doesnt work for the proposed problem - sorry 🙂
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
You solution doesnt work for the example - sorry 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |