Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YellowSquirrel
Frequent Visitor

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 feeding through.

 

One issue I have come across due to the quality of the data is that some of the Employee Numbers and Names are logged as "0". I've given an example of the type of tables that I am looking at (table 1 is an example of issues with the data and table 2 is the summarized table):

Table 1 
Employee NumberEmployee Name
1John Smith
1John Smith
1John Smith
0John Smith
5Sally Evans
5Sally Evans
00
0Sally Evans
2James Grey
3Tom Tool
4Ryan Poole

 

Summarized Table 2
Employee NumberEmployee Name
1John Smith
2James Grey
3Tom Tool
4Ryan Poole
5Sally Evans

 

I am looking to create two columns one for Employee Number and the other for Employee Name:

  • if in table 1 the employee number and employee name = 0 then equals 0
  • if in table 1 the employee number = 0 but the employee name doesn't equal 0 then the employee number = table 2 Employee Number
  • if in table 1 the employee number doesn't equal 0 but the employee name = 0 then the employee name = table 2 Employee Name
  • Else Equal to table 2 Employee Number or table 2 Employee Name (dependant on whether it's for the Name or Number)

I hope this makes sense but let me know if it's not clear.

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

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:

BA_Pete_0-1681741172181.png

 

...to this:

BA_Pete_1-1681741196807.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

Ah ok apologies, I'll repost there.

View solution in original post

8 REPLIES 8
YellowSquirrel
Frequent Visitor

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:

BA_Pete_0-1682072552963.png

Also, this part doesn't do anything:

BA_Pete_0-1682072997378.png

 

I think you want to be looking up the [Employee Number] based on an [Employee NAME] match.

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




v-cgao-msft
Community Support
Community Support

Hi @YellowSquirrel ,

Please try:

Employee Number 2.

if [Employee Number] = 0 and [Employee Name] = "0"
then 0
else if [Employee Number] = 0 
then Table.SelectRows(Table2,(x)=>x[Employee Name]=[Employee Name])[Employee Number]{0}
else [Employee Number]

Employee Name 2.

if [Employee Number] = 0 and [Employee Name] = "0"
then 0
else if [Employee Name] = "0"
then Table.SelectRows(Table2,(x)=>x[Employee Number]=[Employee Number])[Employee Name]{0}
else [Employee Name]

vcgaomsft_0-1681882343845.png

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Hi, I tried to recreated your calculation but I'm not sure what some of it means. When I download your pbix file it says you're on a newer version of power BI than myself. I've looked at it and it just pulls through the columns as columns so I can't see how you've calculated it, maybe it's due to the difference power bis?

Can I ask what do you mean by as I wasn't sure

(x)=>x

and I can't quite do the calculation as you've written it with the AND so I've started it as: If(Table 1[Employee Name] = 0 && Table 1[Employee Number]=0, 0

Does this look correct?

BA_Pete
Super User
Super User

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:

BA_Pete_0-1681741172181.png

 

...to this:

BA_Pete_1-1681741196807.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete, 

Thanks for the recommendation although for me to merge these it causes a lot of issues with the rest of the data/tables/calculations already in the dashboard so I'm hoping to resolve it with a DAX query instead

 

No problem.

In future, you should post DAX queries to either the Desktop forum or the DAX forum. This is the Power Query forum, so you'll get Power Query answers.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Ah ok apologies, I'll repost there.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors