Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Counting in multiple variables

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

Numeric identifierPersona1Persona2Persona3Persona4
58654393JuanFranFran
39085030MaryMaryJuanFran
48593403AnaGemGem
38974340FranJuanMaryMary

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

  • 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

     

     

  • Thanks a lot

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

    • grantsamborn's avatar
      grantsamborn
      Solution Sage

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