How to create calculation from two data sources on Power BI?

[dcheng029]

Hi team,

I am working with two data sources simplified to the below example:

Data Source 1

Name	Team	Control 1	Control 2	Control 3	Control 4
John Smith	A	C	C		C
Bob Will	A	C		C	C
Melissa Tom	B	C	C	C

Data Source 2

Name	Team	Controls	Date Conducted
John Smith	A	Control 1	05/2024
John Smith	A	Control 3	05/2024
John Smith	A	Control 3	06/2024
Bob Will	A	Control 1	05/2024
Bob Will	A	Control 2	06/2024
Melissa Tom	B	Control 1	05/2024
Melissa Tom	B	Control 1	05/2024
Melissa Tom	B	Control 2	06/2024
Melissa Tom	B	Control 4	06/2024

Data Source 1 shows a list of staff and which controls that are marked competent (C) in. Data Source 2 shows the list of staff and which controls they have conducted.

My goal here is to create a calculation that works out how many controls conducted by each staff (from Data Source 2) was those that were marked competent and incompetent (from Data Source 1).

Ultimately, I would like table visuals somewhat like the below:

Name	No. of Competent Controls Conducted	No. of Incompetent Controls Conducted	Adherance Rate
John Smith	1	2	33%
Bob Will	1	1	50%
Melissa Tom	3	1	75%

Team	Adherance Rate (May 2024)	Adherance Rate (June 2024)
A	66%	0%
B	100%	50%

Is this something that Power BI can support, and if so any guidance and instructions would be greatly appreciated!

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[dcheng029]

Hi Ashish,

Thank you so much for attaching your working file; definitely helpful seeing and replicating what you did!

[Ashish_Mathur]

You are welcome.

[dcheng029]

I was able to replicate your file and have everything working, but when I try to input some more data based off my actual dataset, along with a third data source I am running into some hurdles again.

I created a new forum if anyone has time to look through and advise?

https://community.fabric.microsoft.com/t5/Desktop/How-to-create-report-and-calculations-with-3-main-...

[dulan_kavinda]

Join Both Tablen on Name.

Add below column to Data Source 2 Table,

No. of Competent Controls Conducted =

IF (

    'Data Source 2'[Controls] = "Control 1" &&

    LOOKUPVALUE('Data Source 1'[Control 1], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C"

    ||

    'Data Source 2'[Controls] = "Control 2" &&

    LOOKUPVALUE('Data Source 1'[Control 2], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C"

    ||

    'Data Source 2'[Controls] = "Control 3" &&

    LOOKUPVALUE('Data Source 1'[Control 3], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C"

    ||

    'Data Source 2'[Controls] = "Control 4" &&

    LOOKUPVALUE('Data Source 1'[Control 4], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C",

    1,

    0

)

----------------------------------------------------------

No. of Incompetent Controls Conducted =

IF (

    'Data Source 2'[Controls] = "Control 1" &&

    LOOKUPVALUE('Data Source 1'[Control 1], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C"

    ||

    'Data Source 2'[Controls] = "Control 2" &&

    LOOKUPVALUE('Data Source 1'[Control 2], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C"

    ||

    'Data Source 2'[Controls] = "Control 3" &&

    LOOKUPVALUE('Data Source 1'[Control 3], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C"

    ||

    'Data Source 2'[Controls] = "Control 4" &&

    LOOKUPVALUE('Data Source 1'[Control 4], 'Data Source 1'[Name], 'Data Source 2'[Name]) = "C",

    0,

    1

)

Power BI File Attached - control.pbix

If I answer your question? Mark my post as a solution! Thank You!

[fahadqadir3]

@dcheng029First You need to Unpivot your Data Source 1.
In Power Query, Select the Control 1 to Control 4 columns.
Right-click and choose Unpivot Columns.
Rename the columns appropriately (Control and Competence)

Review the attached screenshot and pbix file. 

Controls.pbix

Hope it works.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!! 

[dcheng029]

Hey @fahadqadir3,

Thanks for the information and assistance!

I tried to follow the measures from @Shivu-2000's below comment as well as your tip to unpivot the controls columns, however when I create the table visual the numbers of Competent Controls Conducted and Incompetent Controls Conducted all seem incorrect (I am working with my true dataset which has a lot more entries within both data sources however the logic is still all the same).

Any ideas on where I have gone wrong?

[Shivu-2000]

Hi @dcheng029 
Follow these steps as:

1. Establish Relationship between both the data sources.

2. Create new Measures as:

• Go to the "Modeling" tab and navigate to "New Measure."
• Measure for Competent Controls Conducted:

Competent Controls Conducted = 
VAR _selectedName = SELECTEDVALUE('Data Source 2'[Name])
VAR _competentControls = 
    CALCULATE(
        COUNTROWS('Data Source 1'),
        FILTER(
            'Data Source 1',
            'Data Source 1'[Name] = _selectedName && 'Data Source 1'[Control 1] = "C"
                || 'Data Source 1'[Control 2] = "C"
                || 'Data Source 1'[Control 3] = "C"
                || 'Data Source 1'[Control 4] = "C"
        )
    )
RETURN
    IF(ISBLANK(_selectedName), BLANK(), _competentControls)

•  Measure for Incompetent Controls Conducted:

Incompetent Controls Conducted = 
VAR _selectedName = SELECTEDVALUE('Data Source 2'[Name])
VAR _conductedControls = COUNTROWS('Data Source 2')
VAR _competentControls = CALCULATE([Competent Controls Conducted])
RETURN
    IF(ISBLANK(_selectedName), BLANK(), _conductedControls - _competentControls)

• Measure for Adherence Rate:

Adherence Rate = 
VAR _competentControls = CALCULATE([Competent Controls Conducted])
VAR _conductedControls = CALCULATE([Incompetent Controls Conducted]) + _competentControls
RETURN
    IF(ISBLANK(_conductedControls), BLANK(), DIVIDE(_competentControls, _conductedControls, 0))

3. Building the Visuals:

• Create a table with "Name" from Data Source 2.
• Add the three measures you created ("Competent Controls Conducted," "Incompetent Controls Conducted," and "Adherence Rate") as separate columns.
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  Happy to help!

[dcheng029]

Hi @Shivu-2000,

Thanks so much for the comprehensive info; much appreciated!

The names from Data Source 1 is actually more relevant than those from Data Source 2, how would i amend the measure to use the names from Data Source 1 instead? I've tried changing from:

VAR _selectedName = SELECTEDVALUE('Data Source 2'[Name])

to:

VAR _selectedName = SELECTEDVALUE('Data Source 1'[Name])

but it messes up the calculations.

Also, my actual dataset has hundreds of Controls, is there a more efficient way to include all controls on the "Competent Controls Conducted" measure of will I just have to type in a line for each control?

Thanks again for your assistance on this one!