Data Being Miscounted Because Calendar Table is Getting Filtered

[danielgajohnson]

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 interviews in a month than we do projects. There are dates when we record many new interviews but no new projects. When I create a table visualization for interviews by Calendar[Date] versus interviews by Interview[Date] the results are way off. How can I work around this?

Thanks,

Daniel

[danielgajohnson · Accepted solution]

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]

@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]

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.