Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hoping someone can help a Newbie with what I hope is a simple solution. I’ve been using SQL for 30+ years, but just started using Power BI a couple weeks ago. I have a situation I know how to handle in an SQL query, but I don’t know how to do what I need in Power BI.
We have a timecard application. Every employee charges to a charge code. Every employee has a contract record. The contract record contains the employee charge rate, SOC code, and start and end dates for each period of a contract. For example, you can see below that Mickey Mouse worked from 1/1/2017 through 12/31/2017 at a rate of 35 with SOC P170. The next year his rate was 45 with SOC code M020.
The Contract table to Timecharge table relationship is many to many. How do I get the ONE contract record that goes with a timecharge record? I need the contract record where an employee’s start and end dates are before and after the charge date, respectively. For example, Mickey’s charge on 2/12/2017 goes with the contract record with a SOC code of P170 because the charge date is within that contract record’s start and end date. But his charge on 4/4/2018 goes with the M020 contract record because of a different date.
How can I accomplish this? Hints, etc, very much appreciated. Please keep it fairly simply since I'm new.
Thank you very much,
Kirt
Solved! Go to Solution.
Hello @Kirt1965
The behavior of a visual is to only show rows where the measures are not blank. We can us that along with a measure to compare the ChargeDate to the Start_Date and End_Date like so.
Match? =
VAR _ChareDate =
SELECTEDVALUE ( TIMECHARGE[ChargeDate] )
RETURN
IF (
NOT ISBLANK (
CALCULATE (
COUNTROWS ( CONTRACT ),
CONTRACT[Emp_Start_Dt] <= _ChareDate,
CONTRACT[Emp_End_Dt] >= _ChareDate
)
),
"Yes"
)
This will show "Yes" only on the matching rows:
I have attached my sample .pbix for you to look at.
Hello @Kirt1965
The behavior of a visual is to only show rows where the measures are not blank. We can us that along with a measure to compare the ChargeDate to the Start_Date and End_Date like so.
Match? =
VAR _ChareDate =
SELECTEDVALUE ( TIMECHARGE[ChargeDate] )
RETURN
IF (
NOT ISBLANK (
CALCULATE (
COUNTROWS ( CONTRACT ),
CONTRACT[Emp_Start_Dt] <= _ChareDate,
CONTRACT[Emp_End_Dt] >= _ChareDate
)
),
"Yes"
)
This will show "Yes" only on the matching rows:
I have attached my sample .pbix for you to look at.
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |