Forum Discussion
Counting in multiple variables
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.
4 Replies
- grantsambornSolution Sage
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
- Syndicate_AdminAdministrator
Thanks a lot
It gives me error in the relationship several to one, it does not allow me to have all active simultaneously
- grantsambornSolution Sage
Only the relationship using 'Table'[Persona1] is active. For the other relationships, I use USERELATIONSHIP in the measure.
- Syndicate_AdminAdministrator
Thank you very much, it works! 😊