Forum Discussion
Display Data with no Values
Hi everyone,
I am stuck at this Power BI issue from a long time and any help would be much appreciated.
So I have a dataset with Department, ID, Year and Status.(Dummy tableis attached below)
I am creating a matrix visualization on Power BI Desktop. I have created a mesure called CountID.
I put Year and Status in the Filters pane, and filtering only records for 2014 and it is giving me the following 2 count, which is absolutely correct (image attached).
But what I want is other departments to appear as well, even though they don't have any value, so they can stay blank. I have tried 'Show Items with No data' but it is not working as well.
I want the empty cells for Department B and D for the below dataset, or even If I can replace the blank with 0.
Any help would be greatly appreciated.
Thanks
| ID | Status | Department | Year |
| 49 | Further Info Required | A | 2014 |
| 50 | Not Implemented | B | 2017 |
| 51 | With Team | B | 2018 |
| 52 | With Team | C | 2014 |
| 53 | Further Info Required | D | 2016 |
| 54 | With Team | A | 2015 |
| 55 | With Team | D | 2017 |
| 56 | With Team | C | 2015 |
| 57 | Implemented | B | 2019 |
| 58 | Further Info Required | A | 2016 |
| 59 | Further Info Required | A | 2017 |
| 60 | Further Info Required | A | 2018 |
| 61 | With Team | A | 2019 |
| 62 | Implemented | A | 2020 |
| 63 | Not Implemented | D | 2019 |
| 64 | Further Info Required | D | 2020 |
| 65 | Further Info Required | D | 2021 |
| 66 | With Team | C | 2018 |
| 67 | Implemented | B | 2021 |
| 68 | Further Info Required | C | 2019 |
| 69 | Implemented | B | 2022 |
| 70 | Further Info Required | B | 2023 |
| 71 | Implemented | C | 2020 |
| 72 | Implemented | B | 2024 |
| 73 | Not Implemented | D | 2022 |
| 74 | Further Info Required | A | 2021 |
| 75 | Further Info Required | A | 2022 |
| 76 | Further Info Required | A | 2023 |
| 77 | Implemented | A | 2024 |
| 78 | Not Implemented | D | 2023 |
| 79 | With Team | A | 2025 |
| 80 | Further Info Required | C | 2022 |
Hi Anonymous ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
1.Create a calculate table
Table 2 = VALUES('Table'[Department])2.Create a measure
Count of ID = IF( COUNT('Table'[Department]) = BLANK(), 0, COUNT('Table'[Department]) )3.Create relationship between two tables
3.Create a matrix using Table2[Department] as column and measure as value
4.Final output
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Hi Anonymous ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
1.Create a calculate table
Table 2 = VALUES('Table'[Department])2.Create a measure
Count of ID = IF( COUNT('Table'[Department]) = BLANK(), 0, COUNT('Table'[Department]) )3.Create relationship between two tables
3.Create a matrix using Table2[Department] as column and measure as value
4.Final output
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
If we want to add other columns beside department can we do that?
Hello, you can create the following measure
IDCount=Countrows(Sheet1)+0
then the matrix will show 0 for the values without data, in case you want to show this as blanks you can use a conditional formatting for the 0s.Hi gadielsolis,
I am using this formula:
CountID = COUNT(Sheet1[ID]) + 0But it doesn't change anything.
