Problem consolidating across tables

[CAVCX]

Hello,

I have two tables in a form as below.

TABLE A - HAS A SINGLE ROW FOR EACH ACCOUNT ID/CUSTOMER NAME

TABLE B - SHOWS PROJECTS MADE BY ANY ACCOUNT WITH EACH ROW BEING A DIFFERENT PROJECT. THE VALUES IN THE COLUMNS FOR VIDEO/VOICE ETC ARE SERVICE ADDED WITH 1 BEING ADDED AND 0 BEING NOT ADDED.

e.g. the first row item is Account ID 199683 and has made a Project Name "com.enablex...." and that project has all three services added to it.

There is a 1 : Many relationship between Table A and Table B.

I want to add two columns in Table A

1. Total_Projects   : This column should COUNT all Projects (rows) of an Account in Table B.
2. Video_Projects : This column should SUM the values in the VIDEO column of Table B for each Account.

Despite the unique fields, I can't use the normal Sum/Count etc functions as it ends up adding the total values and not specific to an account.

In Excel, it would be a simple matter of using a PIVOT table on Table B with Account ID or Account Name as the row field and count/sum the relevant columns. Then I could use that output through vlookup and put the extra columns in Table A

But I have just not been able to do this across tables in BI. 

Can someone explain a way to do this? I'm not familiar with DAX but can make new columns and measures in a basic way.

Thanks

[v-zhangti · Accepted solution]

Hi, @CAVCX 

You can try the following methods.

Total_Projects = CALCULATE(COUNT('Table B'[Account ID]),FILTER('Table B',[Account ID]=EARLIER('Table A'[Account ID])))

Video_Project = CALCULATE(SUM('Table B'[Video]),FILTER('Table B',[Account ID]=EARLIER('Table A'[Account ID])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CAVCX]

Thanks the solution worked!

I realised I need one filter on the data. Do you think you can help with that too?

If I want to make an additional Column, in which I can EXCLUDE a particular Project Name (4th Column of Table B) in the Count, then is there a way to do it? E.g. I want to total the projects which have value of 1, except those which are named "Test"

Thanks,

Regards

[CAVCX]

Thank You. It seems to solve the issue. I will verify the data through sampling just to be sure.