cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate values from two tables based on slicer from one

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.

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
Community Support

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

Regular Visitor

That works for me, thank you!

Super User

Hi @ide ,

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

Thanks,

Pragati

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Regular Visitor

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.