Measure + 0 is adding relationship who doesn't exist between objects

[alapointe]

Hi,

I have 3 tables, Parents, Childs, and Incomes.

They are all 1 to many.

My measure :

SumChildMeasure = SUM(Incomes[Value]) + 0

Now if I show a table with 3 columns, ParentName, ChildName, SumChildMeasure.

I will have combinations of Parent and Child who don't exist.

If I remove the "+ 0", I won't have the Child who doesn't have any incomes.

Exemple :

ParentName/ChildName/SumChildMeasure

ParentA	ParentAChildA	50
ParentA	ParentAChildB	1110
ParentA	ParentAChildC	1110
ParentA	ParentBChildA	0
ParentA	ParentBChildB	0
ParentA	ParentCChildA	0
ParentB	ParentAChildA	0
ParentB	ParentAChildB	0
ParentB	ParentAChildC	0
ParentB	ParentBChildA	100
ParentB	ParentBChildB	2220
ParentB	ParentCChildA	0
ParentC	ParentAChildA	0
ParentC	ParentAChildB	0
ParentC	ParentAChildC	0
ParentC	ParentBChildA	0
ParentC	ParentBChildB	0
ParentC	ParentCChildA	0

The ParentCChildA doesn't have any incomes, but the relation between the ParentC and ParentCChildA exist, so I want to show 0.

Put there is know link between ParentA and ParentBChildA, so I don't want to show 0.

Any clues on how to fix this?

Thanks

[Anonymous]

The measure without the +0 returns a blank - which is automatically filtered out of Power BI reports. But when you add 0 to it, the blanks all becomes zeros - which are NOT automatically filtered out of reports...so you'll see rows show up for every combinations of dimension values.

You didn't really specify what the input data tables look like, but I'm imagining:

A "parent" table that has a parent key and parent name

A "child" table that has the child key, child name and a parent key

An "income" table that has child key and income amounts - presumably there are no rows for children who have zero income (because I think they would have shown up if those existed)

If this is the case, then in Power Query you can "merge" rows into the income table that contain each unique child from the child table along with an income amount of 0 (NOT blank). Then I think you measure will show all children and their relationships to parents, without having to use the +0 in your measure (which hoses everythign  up)

Hope this helps!

Scott

[alapointe · Accepted solution]

I solve my problem using columns at each level, calculating the sum but it less flexible with filters. Mergin rows can't work if you have to many rows, it's also duplicating values.

Thanks for your help