Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Experts
I need your urgent help, I have two tables 1 - Active Users (Servers) 2 - Invoice Data
Active Users (Servers) | |
Date | User ID |
Feb-20 | 126389 |
Feb-20 | 126611 |
Mar-20 | 126389 |
Mar-20 | 126611 |
Apr-20 | 126389 |
Apr-20 | 126611 |
May-20 | 126389 |
May-20 | 126611 |
Jun-20 | 126389 |
Jun-20 | 126611 |
Jul-20 | 126389 |
Jul-20 | 126611 |
Aug-20 | 126389 |
Aug-20 | 126611 |
Sep-20 | 126389 |
Sep-20 | 126611 |
Invoice Data | ||
Date | User ID | Invoice Amount |
Jan-20 | 126389 | $ 10 |
Feb-20 | 126389 | $ 15 |
Feb-20 | 126611 | $ 10 |
Mar-20 | 126389 | $ 20 |
Mar-20 | 126611 | $ 20 |
Apr-20 | 126389 | $ 25 |
Apr-20 | 126611 | $ 30 |
May-20 | 126389 | $ 30 |
May-20 | 126611 | $ 40 |
Jun-20 | 126389 | $ 35 |
Jun-20 | 126611 | $ 50 |
Jul-20 | 126389 | $ 40 |
Jul-20 | 126611 | $ 60 |
Aug-20 | 126389 | $ 45 |
Aug-20 | 126611 | $ 70 |
Sep-20 | 126389 | $ 50 |
Sep-20 | 126611 | $ 80 |
Both tables are separate the result I need the amount in a
Event Date | ||
Date | User ID | Point of Time Invoice Last Invoice |
Feb-20 | 126389 | $10.00 |
Feb-20 | 126611 | $0 |
Mar-20 | 126389 | $15.00 |
Mar-20 | 126611 | $10.00 |
Apr-20 | 126389 | $ 20.00 |
Apr-20 | 126611 | $20.00 |
May-20 | 126389 | $25.00 |
May-20 | 126611 | $30.00 |
Jun-20 | 126389 | $30.00 |
Jun-20 | 126611 | $40.00 |
Jul-20 | 126389 | $35.00 |
Jul-20 | 126611 | $50.00 |
Aug-20 | 126389 | $40.00 |
Aug-20 | 126611 | $60.00 |
Sep-20 | 126389 | $45.00 |
Sep-20 | 126611 | $70.00 |
Solved! Go to Solution.
Step 1---Create a bridge table with unique user id
Step 2 --Create a calculated colum in the Active user table
Note: it is not showing the Feb value for user 126389 because there is no data in the active user table , you can work on the source , but hope you got the context.
Rergards
Vpanchu
Create a calculated column in your "Active user" table as below:
Point of Time Invoice Last Invoice=
LOOKUPVALUE(Invoice_Data[Invoice Amount],Invoice_Data[Date],DATEADD(Active_Users[Date],-1,MONTH))
Make sure you have created relationship between the tables on the userID.
Please let me know if this solves.
Thanks for the help, how can I make the relationship as you can see both tables have multiple User IDs of the same User.
Step 1---Create a bridge table with unique user id
Step 2 --Create a calculated colum in the Active user table
Note: it is not showing the Feb value for user 126389 because there is no data in the active user table , you can work on the source , but hope you got the context.
Rergards
Vpanchu
Hi @vpanchu
Thanks It's working but the problem I am facing is it's taking too much time for calculation. I have data from 2018 and each day I get around 24K rows in Active data and increasing with business. Is that any optimize way to get this data?
Regards
Shan
1- you can try incremental load if you have pro account. (i am not sure What is your subscription)
2- you can Try giving one to many relationship insted of many to many.
3- Try to avoide columns with high cardinality if you dont really require it.
4- Use measures wherever required instead of calculated column.
Regards
Vpanchu
Mark the answer if its working for you, it may help other.
Please give Kudos if it was informative in any way 🙂
Have you tried incremental Refresh , instead of loading all the data every time?
because 24 k rows can be handled by PowerBI very easily its nothing.
Regards
Vinayak
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |