Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |