cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benmcmahon
Helper II
Helper II

How to get 0 to show in values instead of nothing in a Matrix Visual?

Hello,

I am currently making a matrix visual with a value assiociated with a person and a month, however, in the months that show no data I cannot get the matrix visual to show a 0. I have tried using:

measure = 'table[column]' + 0 

and 

Measure = if(isblank('table[column],0,[column]))

but niether fix the problem, they still show no value at all when i use the new measure instead of the original column in the 'Values' section of the visual.

 

I think this may be because there are no 0 values in the underlying table for the column in question?

Thanks in advance, matrix in question pictured below.

 

Screenshot 2022-07-14 161319.png

1 ACCEPTED SOLUTION

Thank you for your response, I have tried it using the sum function and that did not work either.

 

Can I ask what you mean by dimension table and star schema?

 

Thanks

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @benmcmahon ,

I created some data:

vyangliumsft_0-1658114778100.png

Fact Table:
It contains all the primary keys of the dimension and associated facts or measures(is a property on which calculations can be made) like count of items, total sales, average of sales etc.
Dimension Tables:
Dimension tables provides descriptive information for all the measurements recorded in fact table.
Dimensions are relatively very small in comparison of fact table.

A fact table holds the data to be analyzed, and a dimension table stores data about the ways in which the data in the fact table can be analyzed.

About star schema, you can check it in this link:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema?source=docs

 

Here are the steps you can follow:

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Group])

vyangliumsft_1-1658114778104.png

2. Join relationship between two tables

vyangliumsft_2-1658114778105.png

3. Create measure.

Measure =
COUNT('Table'[ID]) +0

4. Result:

vyangliumsft_4-1658114845747.png

If you need pbix, please click here.

 

Best Regards,

Liu Yang

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

amitchandak
Super User
Super User

@benmcmahon , hopefully, the measure is like

measure = sum('table[column]') + 0

 

+0 works best when you date, agent etc come from the dimension table and you are in star schema.

 

But some missing combination it will not work

Thank you for your response, I have tried it using the sum function and that did not work either.

 

Can I ask what you mean by dimension table and star schema?

 

Thanks

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.