Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.