How to count the number of values that meet a condition across multiple tables?

[freemainia]

Background data

I have 3 tables:

Project 1. ID Trains Buses Coaches 1 No No No 2 No No Partial 3 Partial No Partial 4 No No No 5 Partial No Partial

Project 2. ID Trains Buses Coaches 1 Yes No Partial 2 Yes No Partial 3 Yes No Partial 4 Partial No Partial 5 Partial No Partial

Project 3. ID Trains Buses Coaches 1 Yes No Partial 2 Yes No Yes 3 No No Partial 4 Partial No Partial 5 Partial No Yes

Problem

I would like to represent this data in a chart (like below), that counts the number of IDs that have a "Yes" on any of the projects I select, the number of IDs that don't have a "Yes" on any of the projects I select but do have a "Partial", and the number of IDs that have neither "Yes" or "Partial" on any of the projects I select.

Put another way, 

If Status= "Yes" for any of the rows of the selected projects with the same ID then count the ID as a "Yes", else

If Status= "Partial" for any of the rows of the selected projects with the same ID then count the ID as a "Partial", else

If Status= "No" for any of the rows of the selected projects  with the same ID then count the ID as a "No".

Fail

I did Append all the Projects, and then use a Calculated Column to calculate what I need. However, I need the measure to respond to the Projects selected.

For example, if I only select Project 1 and Project 2 (on the Slicer), then the measure counts, for each Mode, the number of IDs that have a "Yes" on Project 1 or Project 2, the number of IDs that don't have a "Yes" on Project 1 or Project 2 but do have a "Partial", and the number of IDs that have neither "No" on both Project 1 or Project 2.

[freemainia]

Thanks @v-zhangti .

When I use:

Table = UNION(
SUMMARIZE('Project 1','Project 1'[ID],'Project 1'[Trains],'Project 1'[Buses],'Project 1'[Coaches],"Project","Project 1"),
SUMMARIZE('Project 2','Project 2'[ID],'Project 2'[Trains],'Project 2'[Buses],'Project 2'[Coaches],"Project","Project 2"),
SUMMARIZE('Project 3','Project 3'[ID],'Project 3'[Trains],'Project 3'[Buses],'Project 3'[Coaches],"Project","Project 3"))

I get:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[v-zhangti]

Hi, @freemainia 

You can try the following methods. Join 3 tables into one table.

Table = UNION(
SUMMARIZE('Project 1','Project 1'[ID],'Project 1'[Trains],'Project 1'[Buses],'Project 1'[Coaches],"Project","Project 1"),
SUMMARIZE('Project 2','Project 2'[ID],'Project 2'[Trains],'Project 2'[Buses],'Project 2'[Coaches],"Project","Project 2"),
SUMMARIZE('Project 3','Project 3'[ID],'Project 3'[Trains],'Project 3'[Buses],'Project 3'[Coaches],"Project","Project 3"))

Transform on top of this table. And change the column name to "Status".

Measure:

Yes = CALCULATE(COUNT('Table 2'[ID]),ALLEXCEPT('Table 2','Table 2'[Project]),'Table 2'[Status]="Yes")

No = CALCULATE(COUNT('Table 2'[ID]),ALLEXCEPT('Table 2','Table 2'[Project]),'Table 2'[Status]="No")

Partial = CALCULATE(COUNT('Table 2'[ID]),ALLEXCEPT('Table 2','Table 2'[Project]),'Table 2'[Status]="Partial")

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.

[freemainia]

When I use:

Table = UNION(
SUMMARIZE('Project 1','Project 1'[ID],'Project 1'[Trains],'Project 1'[Buses],'Project 1'[Coaches],"Project","Project 1"),
SUMMARIZE('Project 2','Project 2'[ID],'Project 2'[Trains],'Project 2'[Buses],'Project 2'[Coaches],"Project","Project 2"),
SUMMARIZE('Project 3','Project 3'[ID],'Project 3'[Trains],'Project 3'[Buses],'Project 3'[Coaches],"Project","Project 3"))

I get:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."