Group by another table

[feralvarez994]

Hi everyone! I have a table with number of records (phone calls) by employee and want to group it by another table that have the ranges

The first table contains the employees that i need, the position and the user key in different systems

The second table contains the month/year, the employee key number (related to Identificador_Anura) and the number of calls that every employee have made per month

The lastone contains the position, the month/year (Mes/Año) and the range of calls depending on the position (0 - 200, 201 - 300, 301 - 9999, etc)

I need to multiply the number of calls per employee with the "valor" column in the last table depending on the range that goes between "Cantidad desde" and "Cantidad hasta"

Can someone help me? Thanks a lot!

[tamerj1]

Hi @feralvarez994 

can you please share a screenshot of the visual and explain further the expected results. Also is there any relationship connecting the 3rd table?

[Anonymous · Accepted solution]

HI @feralvarez994,

You can add a calculated column to the second table to lookup values from the third table based on the current 'calls' value:

Result =
CALCULATE (
    MAX ( Table3[valor] ),
    FILTER (
        Table3,
        Table3[Cantidad desde] <= EARLIER ( Table2[Calls] )
            && Table3[Cantidad hasta] >= EARLIER ( Table2[Calls] )
    )
) * Table2[Calls]

Regaeds,

Xiaoxin Sheng