cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ide
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.20221111
02.01.20220222
03.01.20221333
04.01.20220111
05.01.20221222
06.01.20220333
07.01.20221111
08.01.20220222
09.01.20221333
10.01.20221111
11.01.20221222
12.01.20221333

 

Table 2 (have blank rows in Date field):

User idDate
11101.01.2022
22202.01.2022
33303.01.2022
11104.01.2022
22205.01.2022
33306.01.2022
11107.01.2022
22208.01.2022
33309.01.2022
11110.01.2022
22211.01.2022
33312.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 idSum conversions from Table 1
1113
2221
3332

 

Attaching xlsx screenpbi community.jpg file in case of diplays problems.

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
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])))

 

vluwangmsft_0-1643875260108.png

 

Step2,create a new column on table2:

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

vluwangmsft_1-1643875303749.png

 

Then create visual :

vluwangmsft_2-1643875316651.png

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


Best Regards

Lucien

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
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])))

 

vluwangmsft_0-1643875260108.png

 

Step2,create a new column on table2:

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

vluwangmsft_1-1643875303749.png

 

Then create visual :

vluwangmsft_2-1643875316651.png

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


Best Regards

Lucien

Hi @v-luwang-msft 

 

That works for me, thank you!

Pragati11
Super User
Super User

Hi @ide ,

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain

MVP logoDACertAzCert

LinkedIn | Twitter | Blog YouTube 

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

Appreciate your Kudos!!

Proud to be a Super User!!

ide
Regular Visitor

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.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors