Forum Discussion

AFra's avatar
AFra
Helper III
6 years ago
Solved

Filter a table based on information from another table

Hi all, here's my question. 

I have Table A that compiles a series of projects with their status: 

Project IDStatus
Astarted
Bwaiting signature
Cclosed

 

And a table B with details for all the projects : 

Project IDStatusNbr of participants
Astarted32
Awaiting signature26
Aclosed50
Bstarted10
Bwaiting signature10
Bclosed

10

Cstarted

108

Cwaiting signature

100

Cclosed

150

 

I'd like to create a matrice that displays, by Status (from table A) the list of projects & their number of participants. That is : 

 

StatusProject IDNbr of participants
Waiting signatureB10
StartedA32
ClosedC150

 

I tried to use a simple filter on status from table A, but it doesnt work.

Any ideas? 

 

Thanks in advance!! 

Ana 

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi AFra 

    create a column in Table A and set Parameter Row Headers Stepped Layout =Off in visual settings

    Column = CALCULATE(MAX('Table B'[Nbr of participants]),
FILTER('Table B', 'Table B'[Status]=EARLIER('Table A'[Status]) && 'Table B'[Project ID]=EARLIER('Table A'[Project ID])))

     

     

1 Reply

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi AFra 

    create a column in Table A and set Parameter Row Headers Stepped Layout =Off in visual settings

    Column = CALCULATE(MAX('Table B'[Nbr of participants]),
FILTER('Table B', 'Table B'[Status]=EARLIER('Table A'[Status]) && 'Table B'[Project ID]=EARLIER('Table A'[Project ID])))