Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hemingt
Helper V
Helper V

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

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!     

10 REPLIES 10
cnweke
Resolver II
Resolver II

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"))

 

Hello @cnweke 

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?

Could you try it without related then? 

Hi @cnweke 

 

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

360截图--404202781.jpg360截图--404168984.jpg

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?

360截图--169272500.jpg

 

The relationship between these two tables already created.

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.

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.