Calculating Numerator and Denominator using Inactive Relationships

[MiquelPBI]

Hello Community! 

I have two tables: Applications and Calendar.  

The applications table is one row for each application for a job opening.  The Calendar table has one row per date that exists in the data model.  

Applications Table:

Job Opening Id

Application Id

Job Opening Authorization Date

Offer Accepted Date

Calendar Table: 

Date

Relationships

Active = Applications[Job Opening Authorization Date] --> Calendar[Date]

Inactive = Applications[Offer Accept Date] --> Calendar[Date]

Goal

I need to calculate a ratio of applications to job openings.  I've included a screenshot of a scenario with fake data.  My final calc would be total applications divided by total distinct job openings but I want to be able to display the result using the inactive relationship.  I've tried using calculate with userelationship but it only sees the rows where there is an offer accepted.  How do I calculate the total number of applications for the job openings and plot the final result using the inactive relationship on Offer Accepted Date?

[MFelix]

Hi @MiquelPBI ,

Do you want to calculation to be done based on all the calendar selected or just some specific date? Meaning that the Job openings should be counted on teh selected calendar or a different period?

You can create the following two measures:

Ratio = calculate(DIVIDE([Job Openings], Applications[Job Openings accepeted]), ALLSELECTED('Calendar'[Date]))

Ration on chart = IF(MAX('Calendar'[Date]) in ALLSELECTED(Applications[Offer Accepted Date]), calculate(DIVIDE([Job Openings], Applications[Job Openings accepeted]), ALLSELECTED('Calendar'[Date])))

Be aware that in your values you have 9 lines but you only show 8 has final result believe this is a error.

Final result attach and below:

[MiquelPBI]

Thank you for the reply @MFelix !  There are nine rows intentionally.  In this example, I'd want the calculation to ignore the row where job opening ID is 2 b/c does not have an accepted date of 3/15.  The calculation should only count the distinct job opening ids where the offer accepted date is 3/15.  

[v-yanjiang-msft]

Hi @MiquelPBI ,

According to your description, here's my solution.

Create three measures.

Offer accepted =
CALCULATE (
    COUNT ( 'Applications'[Offer Accepted Date] ),
    USERELATIONSHIP ( Applications[Offer Accepted Date], 'Calendar'[Date] )
)

Number of job opening =
CALCULATE (
    COUNT ( Applications[Job Opening Authorization Dae] ),
    ALL ( 'Applications' )
)

Average = DIVIDE([Number of job opening],[Offer accepted])

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[MiquelPBI]

Thank you for the reply @v-yanjiang-msft !  The solution you posted is missing a row in the applications table.  There are six rows where job opening id is 3.  I would like the calculation to only calculate distinct job openings where the offer accepted date is 3/15; therefore excluding the row where job opening id is 2.    

[MFelix · Accepted solution]

Hi @MiquelPBI ,

Sorry but did not understood the data correctly try the following codes:

Job Openings = 
VAR temptable =
    CALCULATETABLE (
        VALUES ( Applications[Job Opening ID] ),
        Applications[Offer Accepted Date] IN VALUES ( 'Calendar'[Date] ),
        USERELATIONSHIP ( 'Calendar'[Date], Applications[Offer Accepted Date] )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Applications[Application ID] ),
        FILTER (
            ALL ( Applications[Job Opening ID] ),
            Applications[Job Opening ID] IN temptable
        )
    )


Job Openings accepeted =
CALCULATE (
    DISTINCTCOUNT ( Applications[Job Opening ID] ),
    USERELATIONSHIP ( 'Calendar'[Date], Applications[Offer Accepted Date] )
)

Ratio =
CALCULATE (
    DIVIDE ( [Job Openings], Applications[Job Openings accepeted] ),
    ALLSELECTED ( 'Calendar'[Date] )
)

Ration on chart =
IF (
    MAX ( 'Calendar'[Date] ) IN ALLSELECTED ( Applications[Offer Accepted Date] ),
    CALCULATE (
        DIVIDE ( [Job Openings], Applications[Job Openings accepeted] ),
        ALLSELECTED ( 'Calendar'[Date] )
    )
)

[MiquelPBI]

@MFelix Thank you!  I changed ALL( 'Applications'[job opening id] ) to ALL( 'Applications') and it appears to work beautifully!  I appreciate the help.