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

[SiroPW]

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.

[V-lianl-msft · Accepted solution]

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.

[watkinnc]

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

[Ashish_Mathur]

Hi,

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

Hope this helps.