Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Kirt1965
Helper III
Helper III

New PowerBI User needs help with a M-M relationship

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.

 

Capture.PNG

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

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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:
2020-04-20_13-40-44.jpg

I have attached my sample .pbix for you to look at.

 

View solution in original post

1 REPLY 1
jdbuchanan71
Super User
Super User

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:
2020-04-20_13-40-44.jpg

I have attached my sample .pbix for you to look at.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.