Forum Discussion
Solved
Multiple column and Table Query Dependant on values
I have a report with multiple tables, I have created as summarized table of the Employee Number and Employee Names Column as I realised that two of the tables aren't matching from the data that is fe...
Hi YellowSquirrel ,
Assuming that your Table2 isn't referenced from Table1, then the simplest and clearest way would probably be to merge Table2 onto Table1 on Table1[EmployeeName] = Table2[EmployeeName] and expand Table2[EmployeeNumber].
Then do the same, but on Table1[EmployeeNumber] = Table2[EmployeeNumber] and expand Table2[EmployeeName].
You can now create two new custom columns, something like this:
employeeNumberClean = if [Employee Number] = 0 then [Table2.Employee Number] else [Employee Number] employeeNameClean = if [Employee Name] = "0" then [Table2.Employee Name] else [Employee Name]I've attached a PBIX below with the query to change this:
...to this:
Pete
Ah ok apologies, I'll repost there.
So far I have gotten to the following but come up with the error: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
Employee Number New =
IF(Table 1[Employee Number]=0 && Table 1[Employee Name] = 0,
0,
IF(Table 1[Employee Number]=0,
LOOKUPVALUE(Table 1[Employee Number],Table 2[Employee Number],Table 2[Employee Number])))
Try adding speech marks here:
Also, this part doesn't do anything:
I think you want to be looking up the [Employee Number] based on an [Employee NAME] match.
Pete