Get distinct values and lookup count

[Krisztian]

Hi everyone,

I have the below scenario where the employees are listed with their current and next managers.

From this i would need to get the distinct values of the managers and then count the number of current and future employees belonging to them as below. Is there any way to do it with DAX functions. I already did the part for getting the unique values by creating a new table with UNION(SELECTCOLUMNS...), but not able to do the lookup count similar to the Excel COUNTIF function.

Thanks.

[SabineOussi · Accepted solution]

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!

[Krisztian]

Thanks a lot for your assistance!

[v-yulgu-msft · Accepted solution]

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

[SabineOussi · Accepted solution]

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!

[tomijs]

Hi, but how do you think is it possible to put pivot on pivot, I mean if we have XX-4, XY-4, ZX -5 and put these figures on graph on x-axis and on y - axis count of current managers or next managers, like I have in the photo?

[SabineOussi]

Can you please explain what the bars and line are?

Are the bars the count of employees by current manager and the line the count of employees by next managers?

So you want something like this?

[tomijs]

Will try to exlain you in different way, step by step!

First of all, I have some data:

Order No.	Product
5754305	x
5754305	y
5754305	y
5754306	yy
5754306	x
5754307	z
5754307	y
5754307	z
5754307	x
5754307	yy
5754308	x
5754308	y
5754308	y

I am preparing in excel pivot and get such data:

Order	Count of Product
5754305	3
5754306	2
5754307	5
5754308	3
Grand Total	13

But my goal is to make in POWER BI count of product on x-axis and count of orders on y-asis, like this:

So it means that I have 1 order with 2 items in it, 2 orders with 3 items and 1 order with 5 items

So I really want to exclude the step of making a pivot in excel, but from this data source at once make a graph like I already mentioned. Hope You can help me!:)

[Vvelarde]

@tomijs

in Edit Query to can Transform to make a Group By

The result is:

Finally in a Table visual:

[SabineOussi]

Agree with @Vvelarde

That's a different scenario now.

[tomijs]

Hi,

But, for example, if I need these figures that were counted: XX - 4, XY - 4 , ZX - 5 to put in graph with figures on x-axis count of empleyees. How do you think, is it possible to put pivot on pivot?