Calculate values from two tables based on slicer from one

[ide]

Hi guys,

Need some help. I hope, i can explain what exactly i want to do.

I have two tables:

Table 1:

01.01.2022	1	111
02.01.2022	0	222
03.01.2022	1	333
04.01.2022	0	111
05.01.2022	1	222
06.01.2022	0	333
07.01.2022	1	111
08.01.2022	0	222
09.01.2022	1	333
10.01.2022	1	111
11.01.2022	1	222
12.01.2022	1	333

Table 2 (have blank rows in Date field):

User id	Date
111	01.01.2022
222	02.01.2022
333	03.01.2022
111	04.01.2022
222	05.01.2022
333	06.01.2022
111	07.01.2022
222	08.01.2022
333	09.01.2022
111	10.01.2022
222	11.01.2022
333	12.01.2022
111
222
333
111
222
333

Also i have to use slicer based on Table2.Date for entire report. 

For KPI purposes i need to add calculated measure based on another table but working with my slicer. 

Expected result: 

Slicer on Table 2.Date - from 01.01.2022 to 10.01.2022

Table2.User id	Sum conversions from Table 1
111	3
222	1
333	2

Attaching xlsx screen file in case of diplays problems.

[v-luwang-msft · Accepted solution]

Hi @ide ,

Try to do like the below:

Step1 ,create a new column on table1:（Aggregate Table 1, and if there are multiple values for the same date, aggregate and.）

sumconversioneveryuserid = CALCULATE(SUM(Table1[Conversion]),FILTER(ALL(Table1),Table1[Date]=EARLIER(Table1[Date])&&Table1[user ud]=EARLIER(Table1[user ud])))

Step2,create a new column on table2:

Column = LOOKUPVALUE(Table1[sumconversioneveryuserid],Table1[user ud],Table2[User id],Table1[Date],Table2[Date])

Then create visual :

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[ide]

Hi @v-luwang-msft 

That works for me, thank you!

[Pragati11]

Hi @ide ,

Create a relationship between your both tables based on User ID column.

Thanks,

Pragati

[ide]

Hi @Pragati11 

That's first thing that i've did. For report purposes this connection should be, but i cannot get my result. Would like to mention that i have to use sliced only from table2.

I also tried to create inactive connection between tables and create a measure

(test = CALCULATE(SUM('Table1'[Conversion]),USERELATIONSHIP('Table1'[Date],'Table2'[Date])))

- nothing.

And i cannot use custom calendar table, cuz these two tables already have connection between themselfs.

As a workaround - it can be something like CALCULATE(COUNT(Table2.UserID),Table2.Date = BLANK()) but if i choose something in slicer based on Table2.Date - that formula should take "dates record" from Table1. That should work for me too.