Forum Discussion
dcheng029
Helper II
Helper IIHow 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 ...
Hi,
PBI file attached.
Hope this helps.
dulan_kavinda
Helper I
Helper IJoin 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!