New PowerBI User needs help with a M-M relationship

[Kirt1965]

Hello All,

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

[jdbuchanan71 · Accepted 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.