Display Data with no Values

[asad-mustafa-]

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

[v-heq-msft · Accepted solution]

Hi @asad-mustafa- ,

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

[asad-mustafa-]

Hi @v-heq-msft 

Thanks a million for the solution. It fixed my issue.

Highly appreciated.

Regards,

Asad

[gadielsolis]

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.

[asad-mustafa-]

Hi @gadielsolis,

I am using this formula:

CountID = COUNT(Sheet1[ID]) + 0

But it doesn't change anything.