Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a fact table and two dimension tables in the Power BI dataset:
The Employee Dimension (In this case is the same person with different IDs)
Sk_Employee | Employee_Id | Employee_Name | Active | Department | Condition | Start_Date | End_Date |
12689 | 100863 | Jonh Doe | 0 | Manufacture | Temporary | 2020-11-01 | 2021-02-01 |
12690 | 100863 | Jonh Doe | 0 | Sales | Temporary | 2021-02-01 | 2099-01-01 |
12691 | 5682 | Jonh Doe | 1 | Engineering | Effective | 2021-02-08 | 2021-05-10 |
12692 | 5682 | Jonh Doe | 1 | Sales | Effective | 2021-05-10 | 2099-01-01 |
The Date Dimension
Sk_Date | Date | Year | Year_Month |
20200101 | 2020-01-01 | 2020 | 2020M01 |
20200102 | 2020-01-02 | 2020 | 2020M01 |
... | ... | ... | ... |
20211230 | 2021-12-30 | 2021 | 2021M12 |
20211231 | 2021-12-31 | 2021 | 2021M12 |
The Fact Table
Sk_Data | Sk_Employee |
20201101 | 12689 |
20201102 | 12689 |
20201103 | 12689 |
... | ... |
20210201 | 12690 |
20210202 | 12690 |
20210203 | 12690 |
... | ... |
20210208 | 12691 |
20210209 | 12691 |
20210210 | 12691 |
... | ... |
20210510 | 12692 |
20210511 | 12692 |
20210512 | 12692 |
... | ... |
The join to populate the fact table consider the date range between Start_Date and End_Date, something like:
SELECT
Sk_Date
,Sk_Employee
FROM
DateDimension AS DD
LEFT JOIN EmployeeDimension AS ED
ON DD.Date >= ED.Start_Date
AND DD.Date <= ED.End_Date
In the Power BI there is a calaculated measure to count distinct employee id in fact table:
EmployeeQty = CALCULATE(
DISTINCTCOUNT(
'EmployeeDimension'[Employee_Id]
)
)
In a Power BI Matrix visualization I have the result below (considering in matrix rows [Employee_Name], [Employee_Id] and [Condition]. For Columns date from dimension date [Year_Month] and for values the calculated measure [EmployeeQty]):
Employee_Name | 2020M11 | 2020M12 | 2021M01 | 2021M02 | 2021M03 | 2021M04 | 2021M05 |
+ Jonh Doe | 1 | 1 | 1 | 2 | 1 | 1 | 1 |
+ 5682 | 1 | 1 | 1 | 1 | |||
Effective | 1 | 1 | 1 | 1 | |||
+ 100863 | 1 | 1 | 1 | 1 | |||
Temporary | 1 | 1 | 1 | 1 | |||
Total | 1 | 1 | 1 | 2 | 1 | 1 | 1 |
So, now I need to create a measure to avoid overlap in month with changes. The rule is: If the change occurred in the last day of month you can consider the count for the month, else month - 1. In other words, if in the example of Jonh the Condition transition occurred in the last day of month (in this example 2021-02-28) I can consider the value 1 for February, else only until January. The problem is to change to Temporary to Effective Jonh was fired as Temporary and Hired as Effective with new ID and the End_Date in EmployeeDimension have the last record for each ID with 2099-01-01 data.
The desired result in Power BI Matrix is (difference is in the column 2021M02):
Employee_Name | 2020M11 | 2020M12 | 2021M01 | 2021M02 | 2021M03 | 2021M04 | 2021M05 |
+ Jonh Doe | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+ 5682 | 1 | 1 | 1 | 1 | |||
Effective | 1 | 1 | 1 | 1 | |||
+ 100863 | 1 | 1 | 1 | ||||
Temporary | 1 | 1 | 1 | ||||
Total | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
I tried to create the measure:
NewEmployeeQty = CALCULATE(
DISTINCTCOUNT(
'EmployeeDimension'[Employee_Id]
),FILTER(
'EmployeeDimension','EmployeeDimension'[End_Date] < EOMONTH('EmployeeDimension'[End_Date].[Date],0)
)
)
But the result is the same of measure EmployeeQty. Does anybody know how to create this measure? Due the End_Date have 2099-01-01 value, I'm trying to use the Start_Date column too.
Solved! Go to Solution.
Hi @TheoC, I couldn't apply the workaround using DAX (by my limitation at this moment), but I could get the result appliyng case when in fact load where clausule. Anyway thank you very much for your time in share knowlodge!
SELECT
Sk_Date
,Sk_Employee
FROM
DateDimension AS DD
LEFT JOIN EmployeeDimension AS ED
ON DD.Date >= ED.Start_Date
AND DD.Date <= CASE WHEN ED.End_Date < EOMONTH(ED.End_Date,0) THEN DATEADD(MONTH, -1, ED.End_Date) ELSE ED.End_Date END
Hi @Anonymous
Can you try something like this:
EmployeeQty =
VAR _DistinctQtyEmp = DISTINCTOUNT ( 'EmployeeDimension'[Employee_Id] )
RETURN
CALCULATE ( _DistinctQtyEmp ,
FILTER ( 'Employee Dimension' ,
'Employee Dimension'[End_Date] <= EARLIER ( 'Date Dimension'[Date] ) &&
'Employee Dimension'[End_Date] >= ( 'Date Dimension'[Date] ) )
)
Apologies, I haven't tested the syntax. I will take a better look shortly.
Thanks heaps,
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi @TheoC, I tried to use but the EARLIER function don't accept value from Date Dimension (Parameter is not the correct type). I created a new variable but without success:
EmployeeQty =
VAR _DistinctQtyEmp = DISTINCTOUNT ( 'EmployeeDimension'[Employee_Id] )
VAR _Date = VALUES(''Date Dimension'[Date].[Date])
RETURN
CALCULATE ( _DistinctQtyEmp ,FILTER ( 'Employee Dimension' ,
'Employee Dimension'[End_Date] <= EARLIER ( _Date ) &&
'Employee Dimension'[End_Date] >= ( _Date )
)
)
Hi @Anonymous , did you use it as a measure or calculated column? EARLIER only works with Calculated Columns. If it has to be measure, we'll need to modify.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@Anonymous did it end up working?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@TheoC not working yet, I created a column with last day of month from End_Date. I'm trying to create a measure similar your example case when End_Date < Last_Month_Date then DATEADD End_Date, -1, MONTH.
Sk_Employee | Employee_Id | Employee_Name | End_Date | Last_Month_Date |
12689 | 100863 | Jonh Doe | 2021-02-01 | 2021-02-28 |
12690 | 100863 | Jonh Doe | 2099-01-01 | 2099-01-31 |
12691 | 5682 | Jonh Doe | 2021-05-10 | 2021-05-31 |
12692 | 5682 | Jonh Doe | 2099-01-01 | 2099-01-31 |
Hi @TheoC, I couldn't apply the workaround using DAX (by my limitation at this moment), but I could get the result appliyng case when in fact load where clausule. Anyway thank you very much for your time in share knowlodge!
SELECT
Sk_Date
,Sk_Employee
FROM
DateDimension AS DD
LEFT JOIN EmployeeDimension AS ED
ON DD.Date >= ED.Start_Date
AND DD.Date <= CASE WHEN ED.End_Date < EOMONTH(ED.End_Date,0) THEN DATEADD(MONTH, -1, ED.End_Date) ELSE ED.End_Date END
@Anonymous that's very strange. I will take a closer look when in front of computer. Currently on phone, apologies!
@amitchandak, is this something you could assist with? Apologies for tagging you. However, I trust your knowledge.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |