Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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?
Solved! Go to 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] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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.
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.
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.
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] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Thank you! I changed ALL( 'Applications'[job opening id] ) to ALL( 'Applications') and it appears to work beautifully! I appreciate the help.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.