Search code examples
excelpowerbidaxpowerquery

The "Aggregate" function from excel to DAX power BI, or search for the maximum date with conditions from another table

I have 2 tables A and B. A has a one-to-many relationship with B. Table B has the start and end dates. Table A is created from Table B, grouped by the column "work group" (group_work_sch = SUMMARIZE('Schedule of types of work', [Section], [Group of works])) In table A, you need to pull up the minimum date from table A, taking into account the group of works. In excel i could do it like: =aggregate(15;6;'Table_B'!$D$3:$D$1173/('Table_B'!$B$3:$B$1173='table_A'!B2);1) expamle: Table B: enter image description here

Table A as result: enter image description here

How could i do it in power bi DAX? now, i create table A and link it to table B. I have to add 2 columns in table A and get dates. enter image description here

Solution

  • Simply try:

    First Start Date = CALCULATE( MIN(TableB[Start Date]) )

    Last End Date = CALCULATE( MAX(TableB[End Date]) )