Forum Discussion

danielgajohnson's avatar
danielgajohnson
Icon for Helper II rankHelper II
5 years ago
Solved

Data Being Miscounted Because Calendar Table is Getting Filtered

Hi All, I have a calendar table connected to my projects table. Each project has multiple interviews so I have an interviews table connected to my projects table. We record about 10 times more inter...
Need Help
  • danielgajohnson's avatar
    danielgajohnson
    5 years ago

    I believe I found the answer in the USERELATIONSHIP DAX function. I created an inactive relationship between the Interviews[Date] and the Calendar[Date] and made a separate measure that uses USERELATIONSHIP. The data matches my pivot table checks.

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

danielgajohnson 

To streamline the model, always make sure there a project for each interview. To do that, create a Dummy Project like in the Projects table and select it for interviews that have no projects.

  • danielgajohnson's avatar
    danielgajohnson
    Icon for Helper II rankHelper II
    5 years ago

    We record projects and interviews in a Quickbase app where Projects is the parent table and Interviews is the child table. It's impossible to record an interview that isn't related to a project. All interviews are related to a project. That's the field that connects the Interviews table to the Projects table in Power BI. Spelled out, the problem is that if Project A is opened on Monday and Project B is opened on Friday then any interviews done for Project A between Tuesday and Thursday won't show up if I'm using the calendar table that's connected to the Projects table.