Count of occurrences based on multiple columns

[Sachintha]

I have a dataset that looks like this. Only the columns relavant to this question are listed here.

ID,Person1,Sign1,Person2,Sign2,Person3,Sign3
1,A,TRUE,X,TRUE,M,FALSE
2,B,TRUE,Y,TRUE,,FALSE
3,C,TRUE,,FALSE,,FALSE
4,D,TRUE,X,TRUE,N,TRUE
5,A,FALSE,Y,FALSE,,FALSE
6,B,TRUE,,FALSE,,FALSE
7,D,TRUE,,FALSE,,FALSE
8,D,TRUE,X,TRUE,,FALSE
9,A,TRUE,Z,TRUE,M,TRUE
10,A,TRUE,Y,FALSE,N,TRUE
11,C,FALSE,Z,FALSE,,FALSE
12,D,FALSE,,FALSE,,FALSE
13,B,TRUE,X,FALSE,N,TRUE
14,A,TRUE,X,TRUE,,FALSE
15,C,TRUE,Y,TRUE,,FALSE

Think of this as an 'approval' sheet. There are 3 persons, and spaces for each of them to sign their name. The Person1 column will always have a name, but the other two columns may or may not have names. Each of the 3 'Sign' columns are set to FALSE by default, and when they sign an item it'll be set to TRUE. So, the Person1 sign will go into Sign1, the Person2 sign will go into Sign2, etc.

An item is considered approved if all persons attached to it have signed their respective names. In other words, if a Person value is not blank, then the respective signature needs to be true.

So, once imported, I create the following 'Unapproved' column in DAX to identify the unapproved items:

Unapproved = 
    (Approvals[Person1] <> "" && Approvals[Sign1] = FALSE()) ||
    (Approvals[Person2] <> "" && Approvals[Sign2] = FALSE()) ||
    (Approvals[Person3] <> "" && Approvals[Sign3] = FALSE())

This gives me a final table that looks like this, filtered to show only the 'Unapproved = TRUE' items:

Now, my task is to createa  table/chart in PBI to show the names of persons listed in Person1, 2, and 3 columns, and show each person's unapproved item tally. Important to note that I want to show each individual who has an approval pending, not the number of unapproved items they're involved with. For instance, in the above example, I want to count only ID=5 against Person A's name, because although that person is involved in two other items that are unapproved (1 and 10), in both of those cases Person A has approved their own name.

So, the table I'm looking for should look something like this:

How do I go about doing this?

[JamesFR06 · Accepted solution]

Hi

I have created a new table fom your original table. Your original table name is Person here.

Person sorted =

Var tab1=SUMMARIZE(Person,Person[ID],Person[Person1],Person[Sign1])

    Var tab2=SUMMARIZE(Person,Person[ID],Person[Person2],Person[Sign2])

    var tab3=SUMMARIZE(Person,Person[ID],Person[Person3],Person[Sign3])

    var _step1= union(tab1,tab2)

    var _step2=union(_step1,tab3)

    var result=filter(_step2,Person[Person1]<>"")

    RETURN

    result

after i have created a measure 

Nbr of false = calculate(count('Person sorted'[ID]),'Person sorted'[Sign1]=false)

and at the end I made a matrice with the new table and the measure