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 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:
User | GoalMonth | GoalVisits |
A | 2020-01-01 | 5 |
A | 2020-02-01 | 6 |
B | 2020-01-01 | 4 |
B | 2020-02-01 | 4 |
B | 2020-03-01 | 3 |
C | 2020-03-01 | 8 |
Table2
User | VisitDate | VisitID |
A | 2020-01-14 | 2 |
A | 2020-01-25 | 4 |
A | 2020-02-18 | 3 |
B | 2020-01-14 | 1 |
B | 2020-02-25 | 5 |
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).
First I added new VisitDateMonth column to Table2:
= Table.AddColumn(Source, "VisitDateMonth", each #date(Date.Year([VisitDate]), Date.Month([VisitDate]),1))
Table2 (modified)
User | VisitDate | VisitID | VisitDateMonth |
A | 2020-01-14 | 2 | 2020-01-01 |
A | 2020-01-25 | 4 | 2020-01-01 |
A | 2020-02-18 | 3 | 2020-02-01 |
B | 2020-01-14 | 1 | 2020-01-01 |
B | 2020-02-25 | 5 | 2020-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?
Solved! Go to Solution.
is this what you want? pls see the attachment below
Proud to be a Super User!
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.
Tried it before (old table in my example pbix). My problem still remains, how to count goals per year per user and Goal%.
what's the expected output based on your sample data?
Proud to be a Super User!
I updated my first post with expected goal picture and example pbix
is this what you want? pls see the attachment below
Proud to be a Super User!
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
Proud to be a Super User!
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 |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |