cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saule
New Member

Merge 2 fact tables and create measures on employee goals and performance

Hi all,

 

UPDATE1:

1. Added Example pbix with my both attempts (current table merge and old trough relationships)

2. Added expected result visualization (below My Goal)

 

I have 2 fact tables:

Table1 has employee goal data (GoalVisits), where goal target is per month, but GoalMonth format is (yyyy-mm-dd), with day allways 1. Table2 has data about employee visits to costumers, where VisitID is unique and created by date.

 

Table1:

UserGoalMonthGoalVisits
A2020-01-015
A2020-02-016
B2020-01-014
B2020-02-014
B2020-03-013
C2020-03-018

 

Table2

UserVisitDateVisitID
A2020-01-142
A2020-01-254
A2020-02-183
B2020-01-141
B2020-02-255

 

My Goal:

1. be able to count each employee visits by various data intervals (per month or year or date interval) in visualization, so i could:

2. count each employee goal archievement (total visits per month/GoalVisits).

 

ExamplePic1.jpg

 

First I added new VisitDateMonth column to Table2:

= Table.AddColumn(Source, "VisitDateMonth", each #date(Date.Year([VisitDate]), Date.Month([VisitDate]),1))

 

Table2 (modified)

UserVisitDateVisitIDVisitDateMonth
A2020-01-1422020-01-01
A2020-01-2542020-01-01
A2020-02-1832020-02-01
B2020-01-1412020-01-01
B2020-02-2552020-02-01

 

Then I merged both tables by User, GoalMonth and User, VisitDateMonth. Each user has few rows with same GoalMonth date, but different VisitDate as expected.

Now when I try to filter in visualization by month - VisitID count works fine. But if filtered in matrix by year and larger time period it automatically aggregates at year level.

 

Questions:

1. Did I merged the right way depending on My Goal (above)?

2. How to write measure to count employee visits (so it works on any date range)?

3. How to write measure to check GoalVisits target? Simple measure employee visits/GoalVisits?

1 ACCEPTED SOLUTION

@Saule 

is this what you want? pls see the attachment below





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

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User III
Super User III

Hi,

Ideally you should have a Calendar Table with relationship (Many to One and Single) from the VisitDate column and GoalMonth columns to the Date column of the Calendar Table.  Likewise, create a stanalone User table (with only unique entries) and build a relationship from the 2 tables to the this standaone table.  To any visual, drag Date from the Calendar Table and User from the standalone table.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Tried it before (old table in my example pbix). My problem still remains, how to count goals per year per user and Goal%.

ryan_mayu
Super User II
Super User II

@Saule 

what's the expected output based on your sample data?





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

Proud to be a Super User!




I updated my first post with expected goal picture and example pbix

@Saule 

is this what you want? pls see the attachment below





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

Proud to be a Super User!




View solution in original post

Thanks, thats the idea. I just added extra month column.

I see that you put count on VisitID and sum on GoalVisits in Model. Not sure why - I need to read more tutorials.

you are welcome





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

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors