How to create report and calculations with 3 main datasets?

[dcheng029]

Hi all,

I am working with the below data sources:

Dataset 1 - shows a list of reported controls with the parent/main controls.

Dataset 2 - shows a list of staff and what controls they conducted.

Dataset 3 - shows a list of staff and what controls they are deemed competent (C).

I want to create Power BI calculations to essentially show the number of competent and incompetent cases closed by each staff member as per below:

My main question is how do I tell Power BI the relationship between main and reported control on Dataset 1? As Dataset 2 uses "Reported Control" and Dataset 3 uses "Main Control".

Any help is appreciated, thank you in advance!

[Ashish_Mathur]

Hi,

Share the download link of the PowerBI file.

[dcheng029]

Hi Ashish,

Thanks for the response. Please let me know if below link doesn't work (organisation limits public share permissions).

https://drive.google.com/file/d/1EBc4twnqcdMWc6iO6Mr6gXWDmuYFcBUO/view?usp=share_link

[Ashish_Mathur]

I do not see any similarity between the data in the file and the tables you posted in your original message.  Please simplify the data in the file and show the expected result there.

[dcheng029]

Please see below link, let me know if this is sufficient.

https://drive.google.com/file/d/16RQljs2XWBALDVzKQVbjMU-SSP4yvwCS/view?usp=drivesdk

[Ashish_Mathur]

What is the problem there?  Show/State that clearly.

[dcheng029]

My question and problem was clearly stated in the original post:

My main question is how do I tell Power BI the relationship between main and reported control on Dataset 1? As Dataset 2 uses "Reported Control" and Dataset 3 uses "Main Control".

Dataset 2 uses reported controls (i.e. Control 1A, Control 2B, etc.) whereas Dataset 3 uses main controls (i.e. Control 1, 2, 3, etc.). The control 'families' are dictated on Dataset 1. How do I link Dataset 2 and 3 controls using the logic from Dataset 1.

[bhanu_gautam]

@dcheng029 , First create relationship between Dataset 1 and Dataset 2 and , Dataset 1 and Dataset 3 on the basis of control column

Then create a measure for Competent cases

CompetentCases =
CALCULATE(
COUNTROWS(Dataset2),RELATED(Dataset3[Competency]) = "C"
)

One more for Incompetent cases

IncompetentCases =
CALCULATE(
COUNTROWS(Dataset2),
RELATED(Dataset3[Competency]) <> "C"
)

Then

Create a Table or Matrix Visualization:

Drag the "Staff Member" field from Dataset 2 to the rows of the table or matrix.
Drag the "CompetentCases" and "IncompetentCases" measures to the values section.

[dcheng029]

Thanks for the response!

When trying to create the measure for CompetentCases, an error appears saying "The column 'Dataset3[Competency]' either doesn't exist or doesn't have a relationship to any table available in the current context." Any ideas what this indicates?

I created relationships between Dataset 1 and 2 as well as Dataset 1 and 3 with the only option available but not sure where I have gone wrong.