Companies in different tables.

[Syndicate_Admin]

Good night, I wanted to know if anyone can help me with this little problem, if you can write to me to the in-house I would thank you an amount to be able to know more details of the solution, the problem is as follows, I have the following example tables:

table 1 Companies that made inquiries through a customer service channel

table 2 Companies that made inquiries on a different channel

Example: First table used chat, according to table used calls

Tables can contain the same companies, as a company may have used the two different channels on the same day

I need an array of this style where you can have in a row the different count of companies that made queries on channel 1 and another row where I have the different count of the companies that made queries on channel 2, finally a third row where I can have the count of the different companies that used the two different channels on the same day

In other words, being able to see which of the companies that used a channel for a day made use of a second channel during the same day.

I attach photos from the tables examples and an example of what the visualization should look like at the end, just on day 6 only one company repeats on both channels so it should be 1, I found a solution by joining between tables and validating conditions, but for tables of thousands of records the processing is too much, I would like to know if someone knows a simple method applying some kind of relationship or filter , thank you very much and excuse me if the question is very basic. ☹

[Syndicate_Admin]

Thank you very much, the solution is excellent. A giant hug.

[Anonymous · Accepted solution]

I have assumed that you have a calendar table that is connected to the date field of both T1 and T2 and you are using this Date field on the matrix visual.

Conteo de empressas canal 1 = DISTINCTCOUNT(T1[Empresa])

Conteo de empressas canal 2 = DISTINCTCOUNT(T2[Empresa])

Conteo de empressas utilizaron ambos canales el mismo día = 
VAR Tcanal1 = VALUES(T1[Empresa])
VAR T3 = 
    ADDCOLUMNS(
        Tcanal1,
        "Conteo",
        VAR CE = [Empresa]
        RETURN
        IF(CE in VALUES(T2[Empresa]),1,0)
    )
RETURN
SUMX(T3,[Conteo])

 This will give you the following result.

Because of the filter context from the Calendar table, the cardinality of the tables used in the evaluation of the measures will be limited to that particular date's records. That way the processing load will not be high.

Número de registros de la Tabla 1 en el contexto de filtro actual = COUNTROWS(VALUES(T1))

Número de registros de la Tabla 2 en el contexto de filtro actual = COUNTROWS(VALUES(T2))