Search code examples
daxpowerquerypowerbi-desktop

DAX and PowerQuery Calculate issue

I have two tables. TableA and TableB. In TableB I have a field called 'Status' that contains values "complete", "In Progress"

I want to create the following field in TableA based on the following calculation from DAX, using PowerQuery

CALCULATE(COUNT('Table B'[Status],'Table B'[Status]="Complete")/COUNTROWS('Table B')

The following line is what I put in PowerQuery, but it is not giving me the correct out put.

Table.RowCount(Table.SelectRows(#"Table B",each [Status] = "Complete"))/Table.RowCount(#"Table B")

Solution

  • If you have a relationship between the two tables, then you'll need to factor that in. The following assumes Table A has a column CatA that is joined to Table B on column CatB.

    Custom Column formula:

    let
  relatedRows = Table.SelectRows(#"Table B", (b) => b[CatB] = [CatA]),
  completed = Table.SelectRows(relatedRows,each [Status] = "Complete"),
  out = Table.RowCount(completed) / Table.RowCount(relatedRows)
in
  out

    Full example query for context:

    let
  Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
  #"Added Custom" = Table.AddColumn(Source, "Completed", each 
    let
      relatedRows = Table.SelectRows(#"Table B", (b) => b[CatB] = [CatA]),
      completed = Table.SelectRows(relatedRows,each [Status] = "Complete"),
      out = Table.RowCount(completed) / Table.RowCount(relatedRows)
    in
      out
  , Percentage.Type)
in
    #"Added Custom"