Counting in multiple variables

[Syndicate_Admin]

My data model generates a single table ("DATA"), which contains a series of text variables with people information:

Numeric identifier	Persona1	Persona2	Persona3	Persona4
58654393	Juan	Fran	Fran
39085030	Mary	Mary	Juan	Fran
48593403	Ana	Gem	Gem
38974340	Fran	Juan	Mary	Mary

I need to know how I could count the numerical identifiers based on the person regardless of whether it is stored in the variable person1, person2, person3, person4. That is, I need to get this:

John: 3

Fran: 4

Mary: 4

Ana: 1

Gem: 2

I hope someone can guide me, I'm new to PowerBI. Thanks in advance.

[Syndicate_Admin]

Thanks a lot

It gives me error in the relationship several to one, it does not allow me to have all active simultaneously

[grantsamborn]

Only the relationship using 'Table'[Persona1] is active.  For the other relationships, I use USERELATIONSHIP in the measure.

[Syndicate_Admin]

Thank you very much, it works! 😊

[grantsamborn]

Hi.  I was able to come up with a solution but it might not be the best way to do it since I'm still learning too.

1. Create a dimenstion table for Person.  (I left the blank row in the table for the relationships.)

DimPerson =
SELECTCOLUMNS(
DISTINCT(
UNION(
VALUES( 'Table'[Persona1] ),
VALUES( 'Table'[Persona2] ),
VALUES( 'Table'[Persona3] ),
VALUES( 'Table'[Persona4] )
)
),
"Person", [Persona1]
)

2. Create many-to-one realtionships between the Table[PersonaX] columns and DimPerson[Person].

3. Create a measure to count rows of 'Table'.

ID Count =
VAR _CurrentPerson = SELECTEDVALUE(DimPerson[Person])
VAR _C1 =
COUNTROWS(
FILTER(
'Table',
'Table'[Persona1] = _CurrentPerson
)
)
VAR _C2 =
CALCULATE(
COUNTROWS(
FILTER(
'Table',
'Table'[Persona2] = _CurrentPerson
)
),
USERELATIONSHIP('Table'[Persona2], 'DimPerson'[Person])
)
VAR _C3 =
CALCULATE(
COUNTROWS(
FILTER(
'Table',
'Table'[Persona3] = _CurrentPerson
)
),
USERELATIONSHIP('Table'[Persona3], 'DimPerson'[Person])
)
VAR _C4 =
CALCULATE(
COUNTROWS(
FILTER(
'Table',
'Table'[Persona4] = _CurrentPerson
)
),
USERELATIONSHIP('Table'[Persona4], 'DimPerson'[Person])
)
RETURN
_C1 + _C2 + _C3 + _C4

(Sorry about the formatting)

I get the desired results when I put DimPerson[Person] and [ID Count] in a table.  (Blank row can be eliminated with a visual-level filter.  Also, it could show a count of Blank columns in 'Table'.)

As I said earlier, there are probably a lot of ways that are better.

PBIX: https://1drv.ms/u/s!AnF6rI36HAVkhPEwPhfdqLLaMLwOsg?e=agIbfy