Matrix Visualization

[asad-mustafa-dc]

I have a matrix visualization in power bi. There are 'Dept' in the Rows and 'Year' in the column. And we have 'CountID' in the Values. Now a matrix is being created. There are a total of 8 Dept but only 7 are being shown in visualization becasue the eighth Dept, i.e. Law, does't have any CountID. What i want is to show the Law Dept as well even without any value.

[v-xinruzhu-msft]

Hi @asad-mustafa-dc 

You can refer to the folloiwng solution

Sample data

1.Create a Dept table to contain the eight departments

2.Create a year table

3.Then create a measure

Measure =
VAR a =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Column1] <> BLANK () ),
        [Dept]
    )
RETURN
    IF (
        SELECTEDVALUE ( Dept[Dept] ) IN a,
        MAXX (
            FILTER (
                'Table',
                [Dept]
                    IN VALUES ( Dept[Dept] )
                        && [Year] IN VALUES ( 'Year'[Year] )
            ),
            [Column1]
        ),
        0
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[asad-mustafa-dc]

Hi @devesh_gupta ,

Thanks for your reply. I tried this solution but I cannot use CountID in a new measure as CountID itself is a self created Measure.

[devesh_gupta]

@asad-mustafa-dc Can we try like this if it works for you:

CountIDOrZero = IF(ISBLANK([CountID]), 0, [CountID])

[asad-mustafa-dc]

@devesh_gupta No it's giving same results as before. It's not showing 0 as values where no CountID exists.

[devesh_gupta]

@asad-mustafa-dc Let's try these simpe steps:

1. Select the matrix visual.

2. On the Fields pane, locate the "Dept" field under the Rows section.

3. Click on the dropdown arrow next to the "Dept" field to open its context menu.

4. Check the option that says "Show items with no data." This option will ensure that all departments, even those without any CountID values, are displayed in your matrix visual.

If you find this insightful, please provide a Kudo and accept this as a solution.

[asad-mustafa-dc]

Hi @devesh_gupta I have tried the solution you proposed, but it is not working either. 

[devesh_gupta]

@asad-mustafa-dc Community support has provided some solution above to this problem... Have you tried if that's working or not?

[devesh_gupta]

@asad-mustafa-dc You can create a new measure like this:

CountIDOrZero = IF(ISBLANK(SUM(YourTableName[CountID])), 0, SUM(YourTableName[CountID]))

Drag this measure into the values field. This should force Power BI to display all departments, including Law, even if it doesn't have any CountID values. The measure will return 0 for Law in this case.

If you find this insightful, please provide a Kudo and accept this as a solution.