Forum Discussion

Kirt1965's avatar
Kirt1965
Icon for Helper III rankHelper III
6 years ago
Solved

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 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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.

     

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
6 years ago

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.