Forum Discussion

CAVCX's avatar
CAVCX
Frequent Visitor
3 years ago
Solved

Problem consolidating across tables

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

 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

3 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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's avatar
      CAVCX
      Frequent Visitor
      3 years ago

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

    • CAVCX's avatar
      CAVCX
      Frequent Visitor
      3 years ago

      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