Count of elements from a different table (milions of row)

[Zepher_ALT]

Hello,

I'm stuck on the following situation, i have 3 table and the following model.

Table 1: User --> contains users details such as Id, name, email, and so on

ID	Email	Name
1	blabla@email.com	bla bla
2	john@email.com	john

Table 2: Log of tickets --> log of change status for each ticket. Each operation has performed by a specific user

Ticket ID	user id	operation date	status
1000	1	22/01/2022	open
1000	1	24/01/2022	closed
1001	1	27/01/2022	open
1002	2	28/01/2022	open
1002	2	29/01/2022	closed

Table 3 --> Reopening Ticket --> Contains only the ticket for which the customer is not satisfied after the closed operation

Ticket ID	Complaint Data
1001	29/01/2022
1002	28/01/2022

So what i need is Dax Measure that counts how many complaint (table 3) we have for each user (table 1) with these condition:

Table 2 --> Status = Closed

Table 3 --> Complaint Data > Operation Date

Last but not least, Table 2 and 3 contains milions of rows.

Any Tips?  

Thank you very much!

[Anonymous]

Hi  @Zepher_ALT ,

Measure 1：

Maximum [Complaint Data] when 'Reopening Ticket'[Ticket ID] exists in 'Log of tickets'[Ticket ID] and grouped by 'Reopening Ticket'[Ticket ID]

var _com=
MAXX(FILTER(ALL('Reopening Ticket'),'Reopening Ticket'[Ticket ID] in SELECTCOLUMNS('Log of tickets',"Ticket",[Ticket ID])&&'Reopening Ticket'[Ticket ID]=MAX('Log of tickets'[Ticket ID])),[Complaint Data])

Maximum date grouped by 'Log of tickets' [Ticket ID]

var _max=MAXX(FILTER(ALL('Log of tickets'),'Log of tickets'[Ticket ID]=MAX('Log of tickets'[Ticket ID])),[operation date])

[status] obtained by grouping by 'Log of tickets' [Ticket ID] and 'Log of tickets' [operation date] equal to the largest date for each group

var _maxstatus=MAXX(FILTER(ALL('Log of tickets'),'Log of tickets'[Ticket ID]=MAX('Log of tickets'[Ticket ID])&&'Log of tickets'[operation date]=_max),[status])

when _com > _max and the status of each group's max date is Closed, 1 ,otherwise, 0

IF(    _com > _max && _maxstatus="closed",1,0)

Measuer2：

Add when 'Log of tickets'[user id] is equal to the corresponding 'User'[ID]

Measure 2 =SUMX(FILTER(ALL('Log of tickets'),'Log of tickets'[user id]=MAX('User'[ID])),[Measure 1])

This is the function used in the formula:

VAR keyword (DAX) - DAX | Microsoft Learn

SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn

MAXX function (DAX) - DAX | Microsoft Learn

MAX function (DAX) - DAX | Microsoft Learn

IF function (DAX) - DAX | Microsoft Learn

The specific situation depends on your production environment. I will use my best efforts to meet your current needs.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi  @Zepher_ALT ,

I add some data:

Here are the steps you can follow：

1. Create measure.

Measure 1 =
var _com=
MAXX(FILTER(ALL('Reopening Ticket'),'Reopening Ticket'[Ticket ID] in SELECTCOLUMNS('Log of tickets',"Ticket",[Ticket ID])&&'Reopening Ticket'[Ticket ID]=MAX('Log of tickets'[Ticket ID])),[Complaint Data])
var _max=
MAXX(FILTER(ALL('Log of tickets'),'Log of tickets'[Ticket ID]=MAX('Log of tickets'[Ticket ID])),[operation date])
var _maxstatus=
MAXX(FILTER(ALL('Log of tickets'),'Log of tickets'[Ticket ID]=MAX('Log of tickets'[Ticket ID])&&'Log of tickets'[operation date]=_max),[status])
var _IDCOUNT=IF(
    _com > _max && _maxstatus="closed",1,0)
return
IF(
    MAX('Log of tickets'[operation date])=_max,_IDCOUNT,0)

Measure 2 =
SUMX(FILTER(ALL('Log of tickets'),'Log of tickets'[user id]=MAX('User'[ID])),[Measure 1])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Zepher_ALT]

Hi @Anonymous ,

Thank you very much for you patience and help,really appreciated 🙂

Bye!

[Zepher_ALT]

hello @Anonymous ,

First of all, thank you very much for your help and the time you dedicated to answer to my question.

May I ask you to explain me a little bit more in details the formulas? I have some problems to understand all the steps and the fuctions you use.

Also, do you think the measures would run in a model with milions of rows?

Thank you very much