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.
I am analyzing some HR data at our company. Below is a mock dataset i've been using to figure out these measures. I was able to do what i needed with calculated columns (Earlier function) but, for academic reasons, I'm trying to also do it with measures.
This fact table is a compilation of a snapshot each year for all current employees, which organization (dept) they are part of, and their current level. I like to create the following measures:
1. # of employees that changed Org from the previous year (job changes)
2. # of employees that changed Level from the previous year (promotions)
3. # of employees that joined the company
4. # of employees that left the company
I've made some progress but i think my approach iterating SumX over each employee is flawed, and i lose context on my my tables/charts. It works with Year as the rows, but fails if i use Org, Level, etc. I ended up trying variables to help keep context, which helped but still problems.
dcOrg:=DISTINCTCOUNT(Data[Org])
OrgChange:=Sumx(Values(Data[Employee]), if([dcOrg]=1, 0, [dcOrg]-1))
OrgChanges LastYear=var currentyear=Max(Data[Year]) Return Calculate([OrgChange], Filter(All(Data), Data[Year]=currentyear || Data[Year]=currentyear-1))
Employee | Year | Org | Level |
1 | 2013 | A | P1 |
2 | 2013 | A | P2 |
3 | 2013 | A | P3 |
4 | 2013 | A | P1 |
5 | 2013 | A | P1 |
6 | 2013 | B | P1 |
7 | 2013 | B | P2 |
8 | 2013 | B | P2 |
9 | 2013 | C | P3 |
10 | 2013 | C | P3 |
1 | 2014 | B | P1 |
2 | 2014 | B | P2 |
3 | 2014 | A | P3 |
4 | 2014 | A | P2 |
5 | 2014 | A | P2 |
6 | 2014 | B | P1 |
7 | 2014 | B | P2 |
8 | 2014 | B | P2 |
9 | 2014 | C | P3 |
10 | 2014 | C | P3 |
11 | 2014 | C | P1 |
1 | 2015 | B | P1 |
2 | 2015 | B | P2 |
3 | 2015 | B | P3 |
4 | 2015 | A | P2 |
5 | 2015 | A | P2 |
6 | 2015 | B | P1 |
7 | 2015 | C | P3 |
8 | 2015 | B | P3 |
9 | 2015 | C | P3 |
11 | 2015 | B | P1 |
1 | 2016 | B | P2 |
2 | 2016 | A | P2 |
3 | 2016 | B | P3 |
4 | 2016 | B | P3 |
5 | 2016 | A | P3 |
6 | 2016 | B | P1 |
7 | 2016 | C | P3 |
9 | 2016 | C | P3 |
11 | 2016 | C | P1 |
12 | 2016 | C | P2 |
I also have a Date table (1 year/row). I am to the point of overthinking it, so looking for help. In practice, the snapshots are taken each month, and the final fact table will be 2-3 million rows (i will likely thin it out by removing duplicates as most people don't change often). Given the # of rows, I am looking for a DAX formula that will scale well.
Thank you in advance for any help.
Solved! Go to Solution.
Hi @mahoney,
You can refer to below sample formula to achieve your requirement:
Sample measures:
Leave = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]=current_year-1) var current_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]=current_year) return COUNTROWS(EXCEPT(previous_List,current_List))+0 Joined = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]<current_year) var current_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]=current_year) return COUNTROWS(EXCEPT(current_List,previous_List))+0 LevelChanged = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Level",Employee[Level]),Employee[Year]=current_year-1) var current_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Level",Employee[Level]),Employee[Year]=current_year) return COUNTROWS(previous_List)-COUNTROWS(INTERSECT(current_List,previous_List))+0 OrgChanged = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Org",Employee[Org]),Employee[Year]=current_year-1) var current_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Org",Employee[Org]),Employee[Year]=current_year) return COUNTROWS(previous_List)-COUNTROWS(INTERSECT(current_List,previous_List))+0
Regards,
Xiaoxin Sheng
Hi @mahoney,
You can refer to below sample formula to achieve your requirement:
Sample measures:
Leave = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]=current_year-1) var current_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]=current_year) return COUNTROWS(EXCEPT(previous_List,current_List))+0 Joined = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]<current_year) var current_List=CALCULATETABLE(VALUES(Employee[Employee]),Employee[Year]=current_year) return COUNTROWS(EXCEPT(current_List,previous_List))+0 LevelChanged = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Level",Employee[Level]),Employee[Year]=current_year-1) var current_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Level",Employee[Level]),Employee[Year]=current_year) return COUNTROWS(previous_List)-COUNTROWS(INTERSECT(current_List,previous_List))+0 OrgChanged = var current_year=MAX(Employee[Year]) var previous_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Org",Employee[Org]),Employee[Year]=current_year-1) var current_List=CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED(Employee),"Employee",Employee[Employee],"Org",Employee[Org]),Employee[Year]=current_year) return COUNTROWS(previous_List)-COUNTROWS(INTERSECT(current_List,previous_List))+0
Regards,
Xiaoxin Sheng
Thank you! I had also been trying it with Except() and got it working (new employees example below):
Not in Last Year:=if(and(min(Data[Year])=[MinYearAll], DISTINCTCOUNT(Data[Year])=1), blank(), Countrows(EXCEPT(Values(Data[Employee]), CALCULATETABLE(Values(Data[Employee]), Filter(All(Data), Data[Year]=Max(Data[Year])-1)))))
I also modified the above with ALLEXCEPT() to go after Org Changes and Level Changes:
Org Changes:=if(COUNTROWS(Filter(ALLEXCEPT(Data,Data[Org]), Data[Year]=Max(Data[Year])-1))>0, blank(), Countrows(EXCEPT(Values(Data[Employee]), CALCULATETABLE(Values(Data[Employee]), Filter(ALLEXCEPT(Data,Data[Org]), Data[Year]=Max(Data[Year])-1)))))
The above worked for Org Changes when Org is used as a filter (row/column/slicer); however, i needed to iterate over Org values to force that context everytime:
Org Changes with Sumx:=Calculate(Sumx(Values(Data[Org]), [Org Changes]), Filter(ALLEXCEPT(Data, Data[Org]), Data[Year]<>[MinYearAll]))-[Not in Last Year]
I am a fan of the five-point-palm function (Sumx(Values(), Measure).
Your approach is much better than the above and should scale well (no iterations). For the Org and Level changes, i only needed to subtract the [Leave] measure as they were getting counted in your measures (e.g., only 3 level changes in 2016).
Thank you for the help, and also for the +0 trick in your measures (to avoid blanks).