Forum Discussion

dcheng029's avatar
dcheng029
Helper II
2 years ago
Solved

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

Hi team,

 

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

 

Data Source 1

NameTeamControl 1Control 2Control 3Control 4
John SmithACC C
Bob WillAC CC
Melissa TomBCCC 

 

Data Source 2

NameTeamControlsDate Conducted
John SmithAControl 105/2024
John SmithAControl 305/2024
John SmithAControl 306/2024
Bob WillAControl 105/2024
Bob WillAControl 206/2024
Melissa TomBControl 105/2024
Melissa TomBControl 105/2024
Melissa TomBControl 206/2024
Melissa TomBControl 406/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:

NameNo. of Competent Controls ConductedNo. of Incompetent Controls ConductedAdherance Rate
John Smith1233%
Bob Will1150%
Melissa Tom3175%

 

TeamAdherance Rate (May 2024)Adherance Rate (June 2024)
A66%0%
B100%50%

 

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

Need Help

9 Replies

  • Shivu-2000's avatar
    Shivu-2000
    Responsive Resident
    2 years ago

    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's avatar
      dcheng029
      Helper II
      2 years ago

      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!
  • fahadqadir3's avatar
    fahadqadir3
    Solution Supplier
    2 years ago

    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's avatar
      dcheng029
      Helper II
      2 years ago

      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?

    • dcheng029's avatar
      dcheng029
      Helper II
      2 years ago

      Hi Ashish,

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

       

  • dulan_kavinda's avatar
    dulan_kavinda
    Helper I
    2 years ago

    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!