how to show a line in an array where the relationship between 2 tables is empty

[krimionn23]

Hi,

My problem :

• I have 2 tables for example like this

Table 1 :

Key	Name
A	Mike
B	Alexander
C	Rebecca

Table 2 :

Key	Value	Date
A	10	May 2024
A	20	Jul 2024
B	30	Jan 2024

The relationship between table 1 and 2 is on the key value

In Power BI, i create this array

Key	Sum Value
A	30
B	30

But i want to have this :

Key	Sum Value
A	30
B	30
C	0

C doesn t show cause is not on the table 2. But how to have the C on the array without merge in power query the 2 tables ?

[Musadev]

hi @krimionn23 
You can achieve the desired results in multiple ways.
1- Merging the existing tables into a new table

2- Left outer join to append the empty rows

3- change the context of your filter and add the keys from table 1 to the visual. 

[krimionn23]

This 3 solutions or only 1 solution with 3 steps ? I need a solution without a merge between 2 tables , Only with the data model

[Musadev · Accepted solution]

Hi @krimionn23 

Follow the following steps and compare your results as shown in the screenshots.

1- Create a dummy measure

DummyMeasure = 0

2- Create a second measure to sum the Value in the second table, adding the DummyMeasure as well.

M2 = 
 CALCULATE(
    SUM('Table'[VALUE]) +[Measure],
    CROSSFILTER('Table'[KEY], TBL1_1[KEY], Both)
 )

you will get the desired output as shown below. 

Did I answer your question? Mark my Post as Solution. Thanks