Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Connecting 3+ tables

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 too...
General Comment
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    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.

     

     

parry2k's avatar
parry2k
Super User
7 years ago

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.

 

 

parry2k's avatar
parry2k
Super User
7 years ago

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