Get all the equipments with or without the last user that borrowed it

[Anonymous]

I have the following tables:

Equipments { id, description }
Loans { id, date, equipmentId, userId }
Users { id, name, login }

I need to show all the equipments and the name of the last user that borrowed this equipment. If the equipment has no rows on loans, the equipment still needs to be returned but with a blank user.

I tried using merge query as new, but when I select the users.name column, the number of rows increases, because of the many-to-many relationship.

How can I return only the last user for each equipment or blank?

Sample SQL:

CREATE TABLE equipments(
   id          INTEGER  NOT NULL PRIMARY KEY 
  ,description VARCHAR(8) NOT NULL
);
INSERT INTO equipments(id,description) VALUES (1,'hammer');
INSERT INTO equipments(id,description) VALUES (2,'scissor');
INSERT INTO equipments(id,description) VALUES (3,'pliers');

CREATE TABLE users(
   id    INTEGER  NOT NULL PRIMARY KEY 
  ,name  VARCHAR(8) NOT NULL
  ,login VARCHAR(8) NOT NULL
);
INSERT INTO users(id,name,login) VALUES (1,'juliano','juliano');
INSERT INTO users(id,name,login) VALUES (2,'paul','paul');
INSERT INTO users(id,name,login) VALUES (3,'maria','maria');


CREATE TABLE loans(
   id          INTEGER  NOT NULL PRIMARY KEY 
  ,date        VARCHAR(20) NOT NULL
  ,returnDate  VARCHAR(20)
  ,equipmentId INTEGER 
  ,userId      INTEGER 
);
INSERT INTO loans(id,date,returnDate,equipmentId,userId) VALUES (1,'2019-08-01 12:00:00','2019-08-02 08:00:00',1,2);
INSERT INTO loans(id,date,returnDate,equipmentId,userId) VALUES (2,'2019-08-02 14:00:00','2019-08-02 15:00:00',2,2);
INSERT INTO loans(id,date,returnDate,equipmentId,userId) VALUES (3,'2019-08-01 12:00:00',NULL,3,1);
INSERT INTO loans(id,date,returnDate,equipmentId,userId) VALUES (4,'2019-08-03 12:00:00','2019-08-04 09:00:00',1,1);
INSERT INTO loans(id,date,returnDate,equipmentId,userId) VALUES (5,'2019-08-03 12:00:00','2019-08-03 14:00:00',2,3);

Expected output:

equipmentId, equipmentName, userName
1, hammer, juliano
2, scissor, maria
3, pliers, juliano

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

---

@Ashish_Mathur wrote:

Hi,

Share some data and show the expected result.

---

I've updated the question with sample SQL and expected result.

[amitchandak · Accepted solution]

Check

Max use id = Maxx(filter('test loans','test loans'[equipmentId]='test equipments'[id]),'test loans'[id])
last user = MAXX(filter('test loans','test loans'[id]= 'test equipments'[Max use id] ),'test loans'[userId])

https://www.dropbox.com/s/kv1o2wdzaoqy2l1/equipment.pbix?dl=0

[amitchandak]

I did it using two columns in the item table(like Equipment). Trying to find the customer who has ordered last for an item. In given formula's I wanted to use order date but seems like because of some formatting issue it did not work out.

Max Sales order id = Maxx(filter(sales,Sales[item_id]='Item'[Item ID]),Sales[Order Id]) 
Max customer = MAXX(filter(Sales,Sales[Order Id]='Item'[Max Sales order id] && Sales[item_id]='Item'[Item ID]),Sales[Customer ID])

This can be achived in one formula too, but min or minx is not supported on top of lookup.  The formula given below not working

Last Customer 2 = 
VAR v_table =SUMMARIZE(
        Sales
        ,Sales[item_id]
        ,"SalesMinDate" ,MAX('Order'[Order ID]),
        "minitem",Sales[item_id]
    )
var cust_id =MINX(filter(sales,Sales[item_id]='Item'[Item ID]),LOOKUPVALUE(Sales[Customer ID],Sales[item_id],'Item'[Item ID],Sales[Order Id],minx(filter(v_table,'Item'[Item ID]=[minitem]),[SalesMinDate])))
   
RETURN
cust_id