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 Name Team Control 1 Control 2 Control 3 Control 4 John Smith A C C   C Bob Will ...
Need Help
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!