Forum Discussion
How to show data with zero count in table when filtering date
I have a date slider in my dashboard.
And I have a table visual that contains all products that were sold for a specific date range. (Ex. Products A, B, C, D, E, F).
Now when I do not filter by date, it shows from A-F but when I filter by specific date, products with zero (0) sales will no longer show.
Ex.
No Filter
| Products | Sales Count |
| A | 5 |
| B | 3 |
| C | 2 |
| D | 1 |
| E | 1 |
| F | 5 |
With Date Filter (Products C, and E are not shown)
| Products | Sales Count |
| A | 5 |
| B | 3 |
| D | 1 |
| F | 5 |
Expected Result (it will still show products with 0 sales count)
| Products | Sales Count |
| A | 5 |
| B | 3 |
| C | 0 |
| D | 1 |
| E | 0 |
| F | 5 |
tonton I added a date dimension in your model and linked it with your sales table on the date. Added a measure (I think called Count) and on the slicer used Date from the date dimension.
As a best practice, you always have a star schema in your model and also must add a date dimension in the model for any date-related calculations.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
11 Replies
- parry2k
Super User
tonton I added a date dimension in your model and linked it with your sales table on the date. Added a measure (I think called Count) and on the slicer used Date from the date dimension.
As a best practice, you always have a star schema in your model and also must add a date dimension in the model for any date-related calculations.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
I will try this again sir and will let you know
- parry2k
tonton create a measure and use this in table visual
Count = COUNTROWS ( Table ) + 0Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thanks for this. What's the 'table' you are referring to?
the "table" in my post refers to the table visuals in power bi
yes, i tried using the table (not the visual), but it doesnt work, it just counts the rows, same as my number of sales column. But still the same issue occurs where in, products with ZERO sales do not appear
Products Sales Count A 5 B 3 D 1 F 5
- parry2k
tonton solution attached.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi, thank you so much, but what did you do, what's the solution so I can try it on my own. thank you
