Difficulties relating data across multiple tables

[jeewats]

I am making a productivity dashboard. The team is comprised of a set group of individuals and a group of float/PRN folks that help fill gaps. To identify when a float member worked, there is a spreadsheet on SharePoint that the manager fills out with the teammates name and date they worked. Because a teammate can work multiple days, I created a bridge table that contains the distinct names from that document to join it to my main query. The link is their name as that is attached to the work they do (ignor the errors in the main table; that is where I was trying to break this down into steps and failed).

The piece that falls apart for me is when I try to relate the 'Float Team' table to the 'PICS Data' table. Power BI tells me there is no relationship between them. I've looked through the data to make sure those teammates exist in the main query and they do. Here is the latest version of the formula I tried. I'm getting and error on the line 'PICS Data'[Verified_Date] = RELATED('Float Team'[Worked_Date]) stating "Parameter is not the correct type". If I remove RELATED (because it is the many side of a relationship), I get "Cannot find name [Worked_Date]".

PICS_Filter_3 = 
IF ('PICS Data'[Verifying_Teammate_Name] IN VALUES ('Float Team'[Epic_Name]) &&
    CALCULATE(
         COUNTROWS('PICS Data'),
         USERELATIONSHIP('Float Team Bridge'[Epic_Name], 'PICS Data'[Verifying_Teammate_Name]),
         USERELATIONSHIP('Float Team Bridge'[Epic_Name], 'Float Team'[Epic_Name]),
         'PICS Data'[Verified_Date] = RELATED('Float Team'[Worked_Date])
         ),
   "PICS",
"SITE")

[Anonymous]

Hi @jeewats 

The error message "Parameter is not the correct type" suggests that there's a type mismatch between the columns you're attempting to relate. This can occur if one column is of type text and the other is of type date, for example. Ensure that the data types of the columns you're trying to relate are compatible.

The Use of the RELATED Function: The function is used to fetch a related value from another table in a one-to-many relationship, where it is used within the context of the "one" side. Since you're trying to relate dates from a "many" side, the function isn't the appropriate choice here.

It's important to ensure that the relationships between your 'PICS Data' table, 'Float Team Bridge' table, and 'Float Team' table are set up correctly. Ensure that 'Epic_Name' is the unique identifier across these tables and that they have the correct one-to-many or many-to-one relationship as needed.

Replace the function with a proper filtering mechanism in your DAX formula. Since 'PICS Data' seems to be the fact table and 'Float Team' the dimension table, you might need to use or along with if your relationship is inactive.

Here's a simplified version of the DAX measure you might want to start with:

PICS_Filter_3 = 
IF (
    'PICS Data'[Verifying_Teammate_Name] IN VALUES('Float Team'[Epic_Name]),
    CALCULATE (
        COUNTROWS('PICS Data'),
        USERELATIONSHIP('Float Team Bridge'[Epic_Name], 'PICS Data'[Verifying_Teammate_Name])
        // Add additional filters here if necessary
    ),
    "PICS",
    "SITE"
)

Regards,

Nono Chen

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