Forum Discussion
Connecting 3+ tables
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.
trying to get something like this
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?
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
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.
update table name and field name in dax expression basedo on your model.