Get distinct values and lookup count

Hi Krisztian

One way to do this is to unpivot columns Current Manger and Next Manager.

Go to Edit Queries, select these two columns, go to Transform tab and choose Unpivot Columns

You table should now look like this

Hit Close and Apply for changes to take effect.

From the report page, select the matrix visual and distribute your fileds as below to get the desired view

Make sure you are grouping the Employees by count.

You can always turn off the totals and change the field names.

Hope this helps!

Hi Krisztian,

You can achieve your goal using DAX functions.

In your source table, add two calculated columns:

Count Current =
CALCULATE (
    COUNT ( 'lookUp Count'[Employee] ),
    ALLEXCEPT ( 'lookUp Count', 'lookUp Count'[Current Manager] )
)

Count Next =
CALCULATE (
    COUNT ( 'lookUp Count'[Employee] ),
    ALLEXCEPT ( 'lookUp Count', 'lookUp Count'[Next Manager] )
)

Then, create an auxiliary table:

Table =
UNION (
    SELECTCOLUMNS (
        'lookUp Count',
        "Employee", 'lookUp Count'[Employee],
        "Manager", 'lookUp Count'[Current Manager]
    ),
    SELECTCOLUMNS (
        'lookUp Count',
        "Employee", 'lookUp Count'[Employee],
        "Manager", 'lookUp Count'[Next Manager]
    )
)

Create a new table to dispaly distinct values of the managers and add two calculated columns to count the number of current and future employees.

Table1 = SUMMARIZE('Table','Table'[Manager])

Current =
LOOKUPVALUE (
    'lookUp Count'[Count Current],
    'lookUp Count'[Current Manager], 'Table 1'[Manager]
)

Next =
LOOKUPVALUE (
    'lookUp Count'[Count Next],
    'lookUp Count'[Next Manager], 'Table 1'[Manager]
)

Best regards,
Yuliana Gu