cancel
Showing results for
Did you mean:
Helper I

## Show rows with zero value in table (two underlying data tables)

Hi,

I have two tables:

Table ORG

 id name org 123 a x 456 b y 789 c z

Table HOLDINGS

 id units value 123 50 500 789 20 200

I would like to output a table (or matrix):

 id name org units value 123 a x 50 500 456 b y 0 0 789 c z 20 200

The trick of course is to get the id 456 from the ORG table to show even with no values in the holdings table.

I've tried a calculated column of SUM(value) + 0 but it is still not showing.

I hope that is clear, any help is appreciated.

1 ACCEPTED SOLUTION
Community Support

Hi @SiroPW ,

Create two measures:

Measure = IF(ISBLANK(MAX('Table (2)'[units])),0,MAX('Table (2)'[units]))
Measure 2 = IF(ISBLANK(MAX('Table (2)'[value])),0,MAX('Table (2)'[value]))

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

3 REPLIES 3
Community Support

Hi @SiroPW ,

Create two measures:

Measure = IF(ISBLANK(MAX('Table (2)'[units])),0,MAX('Table (2)'[units]))
Measure 2 = IF(ISBLANK(MAX('Table (2)'[value])),0,MAX('Table (2)'[value]))

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

Solution Sage

Try CALCULATE(SUM[Value]), ALL(Table[ID]), Values(Table[ID]))

Super User III

Hi,

Using the Query Editor, merge the Holdings Table into the Org Table.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements