Connecting 3+ tables

[Anonymous]

I have 3 tables (projects, tool A, tool B) which are connected with project number, and need to get : 

1. total projects

2. projects using tool A

3. projects using tool B

4. projects using tool A and B

5. projects not using A nor B tool

is there an easy way how to do this ?

only way i could think of is to create new table and bring there column projects from projects table (150k rows) and then add column related for A, new column related for B and do Venn diagram ? 

or vlookups ? 

thank you for help

[Anonymous]

Main table is Project .. 2 tools tables Projectsite and PW connected to Projects table .. 

Can I have a Pie chart showing total projects using ProjectSite, Pw, Projectsite and PW and none of those 2 ? 

I managed to connect them with " Style " table . but still not able to get the result i want 

[parry2k]

@Anonymous

Still not fully clear about your requirements.

Are you try to have pie chart based on 

- name from PW and show total distinct projects

- name from PS and show total distinct projects

Is this correct?

[Anonymous]

trying to get something like this 

[parry2k]

can you send some sample data?

Also if a project is in PW and PS, so it will show in 

- project using project sies

- project using pw

- project using project sites and pw

it will account at 3 places, correct?

[Anonymous]

should be 4, 4th is projects without any (ps nor pw)

samble below 

Total projects 20 - projects with ps 10 - projects with pw 6 

Pie needs to show - 3 PW + PS, 3 PW , 7 PS, 7 none 

 https://drive.google.com/file/d/11miEur-FMj7Ev5CJ07d6Jx7aB0don06O/view?usp=sharing

[parry2k · Accepted solution]

@Anonymous try following and tweak it from there :

Add two column in your main project table to define which project exists in which table

Project Exists in PS = 
VAR x = RELATED( PS[ProjectID] )
RETURN IF( x = BLANK(), 0, 1 )

Project Exists in PW = 
VAR x = RELATED(PW[ProjectID] )
RETURN IF( x = BLANK(), 0, 1 )

and then add a table using following DAX, it will put all projects together alongwith whcih category they are in

Total Projects = 
UNION(
ADDCOLUMNS( Project, "Category", "Doesn't exists" ),
ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PS] = 1 && Project[Project Exists in PW] = 1 ) ), "Category", "Exists in Both" ),
ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PW] = 1 ) ), "Category", "Exists in PW" ),
ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PS] = 1 ) ), "Category", "Exists in PS" ) 
)

drop pie chart visual, use category from this new project table "Total Project" and use "count of project id" as value. I think this will get your what you are looking for.

[Anonymous]

Hi experts, 

I have 3 tables which contained item code - name. I can connect either Sell_in[item code] or Sell_out[item code] to Item[Item code]. Not both. 

Could you please help me solve it. Much appriciate

[parry2k]

update table name and field name in dax expression basedo on your model.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[LivioLanzo]

can you share your dataset?