cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate V
Advocate V

Filtering gallery by using information from another datasource

Hello all,

I have a question/problem/need some help regarding some formula I am trying to use. The situation is the following: I have my Excel sheet as datasource 1, which is the main one, that has the following columns (that I am trying to use):

filtro2.png

As can be seen, column A has "project type", column C the name, column D the task type (level 1), column E the task type level 2 and the last column (that does have data, but in other rows) is task level 3.

 

Then, on my second Excel file I have the 'master task data', where it shows all the project types that exist, along with the task it has and also a column that is called 'Dependientes_MT' which has the number of tasks it has in a level below them.

filtro1.png

In the image, it can be seen it is columns A-B-C-D and E.

What I want to do is to filter the data in the first file by using the criteria of 'Dependientes_MT'=0 from the second file, meaning, I want to show only those that do not have 'child tasks' so to speak. So far I have tried:

Filter(Consolidado_Piloto_v6, 
    LookUp(maestro_tareas, TipoProyecto='Tipo Proyecto', 'Tipo Proyecto') = TipoProyecto &&
    If('Nombre Sub-Subtarea'<>"",
        LookUp(maestro_tareas, 'Nombre Sub-Subtarea'='Sub Subtareas (Tarea nivel 3)',
        'Sub Subtareas (Tarea nivel 3)') = 'Nombre Sub-Subtarea',
            If('Nombre Subtarea'<>"",
            LookUp(maestro_tareas, 'Nombre Subtarea'='Subtareas (Tarea nivel 2)', 'Subtareas (Tarea nivel 2)')
            = 'Nombre Subtarea',
            LookUp(maestro_tareas, 'Nombre Tarea'='Tareas (Tarea nivel 1)', 'Tareas (Tarea nivel 1)')
            = 'Nombre Tarea'))
    && LookUp(maestro_tareas, Dependientes_MT="0", 'Tipo Proyecto') = TipoProyecto)
Filter(Consolidado_Piloto_v6, 
    LookUp(maestro_tareas, TipoProyecto='Tipo Proyecto', 'Tipo Proyecto') = TipoProyecto &&
    If('Nombre Sub-Subtarea'<>"",
        LookUp(maestro_tareas, 'Nombre Sub-Subtarea'='Sub Subtareas (Tarea nivel 3)' && Dependientes_MT="0",
        'Sub Subtareas (Tarea nivel 3)') = 'Nombre Sub-Subtarea',
            If('Nombre Subtarea'<>"",
            LookUp(maestro_tareas, 'Nombre Subtarea'='Subtareas (Tarea nivel 2)' && Dependientes_MT="0", 'Subtareas (Tarea nivel 2)')
            = 'Nombre Subtarea',
            LookUp(maestro_tareas, 'Nombre Tarea'='Tareas (Tarea nivel 1)' && Dependientes_MT="0", 'Tareas (Tarea nivel 1)')
            = 'Nombre Tarea')))

However they haven't worked yet, or show items that do not qualify with the " = 0" criteria.

1 REPLY 1
Highlighted
Advocate V
Advocate V

Re: Filtering gallery by using information from another datasource

Another option I tried was:

  • In source 1 have a key made of two columns concatenated.
  • In source 2 have same key made.
  • Do lookup of source 2 key, where key 1 = key 2, retrieve the 'dependientes' one and that one = 0

However, did not work

Helpful resources

Announcements
Check this Out

Announcing

Class of 2020- Season 2

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,961)