How to add a new column with the calculate result from other table?

[hemingt]

Hi ,

I have two tables, such as following,

table A

Week    Feature_Name   Status

W18       Feat-1               New

W18       Feat-2               New

W18       Feat-3               Approve

W19       Feat-1               Approve

W19       Feat-2               Commit

W19       Feat-3               Commit     

table B

Week      Test      Status      Feature

W18       Test-1    Pass        Feat-1

W18       Test-2    Pass        Feat-1

W18       Test-3    Fail          Feat-1

W18       Test-4    Fail          Feat-2

W18        Test-5    Pass       Feat-2

W19       Test-6     Pass       Feat-3

I want to add a new cloumn to table A by using the Power Query Editor, the value is 

count_rows(Table A[Week] == Table B[Week] and Table A[Feature] == Table B[Feature] and Table B[Status]=="Pass")

Do you know how to do it ? Thank you very much!     

[Anonymous]

Hey,

Just do this in a dax measure. Typically things like count, sum, avg, ... are aggregations i.e. they're put in 'one field' and aren't really suitable for columns.

calculate(count(Table A[Week]);Table A[Week] = related(Table B[Week]);A[Feature] = related(Table B[Feature]); Table B[Status]="Pass"))

[hemingt]

Hello @Anonymous 

Thank you for your reply.

it seems that  related(Table B[Week]) is not correct, only measure can be refered by the related function. Can you help to double check?

[Anonymous]

Could you try it without related then? 

[hemingt]

Hi @Anonymous 

it's the same, only measure value can be refered.

[hemingt]

[Anonymous]

So on the lefthand side of power BI you have 3 'tabs' the first one is your visual, the second one is your data and the third one is the relationships tab. Have you already made your relationships between the tables?

[hemingt]

The relationship between these two tables already created.

[hemingt]

as you can see the above two pictures,

one is the relationship of the two columns TestReport[Week] and Executions[Week]

the other picture is to use the calculation, and the error info.

[hemingt]

Could you please give me a demo if possible? Thank you!

[Anonymous]

Wait, are you even making a measure and not just a calculated column. Could you screenshot the entire error message?