Link two fact tables with two dimensions

[TinaH]

Hello,

I am facing the following issue on linking two fact tables with two dimensions:

Lets say I have the following tables:

Fact_1

ID_1	Time_1	Value_1
1	20220404	10
2	20220405	15
3	20220406	5

Fact_2

ID_2	Time_2	Value_2
1	20220404	1
2	20220404	2
3	20220404	8
4	20220405	6
5	20220405	5
6	20220405	2

And a Link Table:

Link

ID_1	ID_2
1	1
1	4
2	2
2	5
3	3
3	6

In addition to the IDs, I want to link the time stamps as well to plot:

x-axis = time

y-axis = sum(Value_1), sum(Value_2)

How can I do this? I tried concatenating the two fact tables, but this is not working

Thank you very much!
Tina

[NitinK7]

Can you please provide what output you want in X-axis and Y-axis based on your given table?

[TinaH]

Hello,

I want the following outputs:

x-axis	y1	y2
20220404	10	11
20220405	15	13
20220406	5	-

[marcus_sommer · Accepted solution]

Concatenating the facts is nearly always the most suitable approach. This means in your case something like:

t: load ID, Time, Value, 'Source1' as Source from Source1;
concatenate(t)
   load ID, Time, Value, 'Source2' as Source from Source2;

and within the UI you could use the source-information to select, to dimension and/or to filter the wanted data-set, like:

sum({< Source = {'Source1'}>} Value)

- Marcus

[TinaHensel]

Thank you very much. With the concatenation, it is working fine!