Count Help

[kl8818]

Hello - needing help formulating a measure to help count for visual.

Goal -

1. Card Visual to show count of how many employees w/ 1 or more logins
2. Card Visual to show percentage of employees w/ 3 or more logins
3. Utilize Silcer to filter by date

Sample data:

Date	Employee ID	User Name	User First Name	User Last Name	User Email
4/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
4/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
4/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
4/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
4/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
5/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
5/1/2023	987654321	Uncle Eddy	Uncle Eddy	Johnson	uncleeddy@christmasvacation.com
5/1/2023	987654321	Uncle Eddy	Uncle Eddy	Johnson	uncleeddy@christmasvacation.com
4/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
4/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
4/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
4/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
4/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
4/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
5/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
5/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
5/1/2023	563214897	Ellen Griswold	Ellen	Griswold	ellengriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
4/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
4/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
4/1/2023	789456132	Rusty Griswold	Rusty	Griswold	rustygriswold@christmasvacation.com
6/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
6/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
6/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
6/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
6/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
6/1/2023	123456789	Clark Griswold	Clark	Griswold	clarkgriswold@christmasvacation.com
4/1/2023	654789213	Audry Griswold	Audry	Griswold	audrygriswold@christmasvacation.com

ATTEMPT 1 ---

Adjustments made in power query: Group by 'Employee ID'

I set up two measurements:

Measure1 = CALCULATE( COUNTA(Sheet1[Count]), Sheet1[Count] >= { 3 })

Measure2 = CALCULATE( COUNTA(Sheet1[Count]), Sheet1[Count] >= { 1 })

Then to gain a percentage, I set up a third measurement for the card visual:

Measure3 = 100*(DIVIDE([Measure1],[Measure2]))

Everything comes out accurate:

However, I am not able to filter by date with slicer because I grouped the information in power query and my date is not longer an option in the data.

ATTEMPT 2 ---

Adjustments made in power query: Group by 'Employee ID', and 'Date'

Visuals updated...no longer accurate. I only have 5 employees in my data.

 ATTEMPT 3 ---

 Adjustments made in power query: Created pivot table

This is where I am stuck. It seems like attempt 3 will provide me the option to slice by date but I am lost on how to create the measurements. When I follow the same measurements above, adjusting for column names, I receive errors. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly,  but please check the below picture and the attached pbix file.

I tried to create a datamodel like below.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Count employee with 1 or more login: = 
VAR _t =
    ADDCOLUMNS (
        VALUES ( Employee[Employee ID] ),
        "@logincount", CALCULATE ( COUNTROWS ( Data ) )
    )
RETURN
    COUNTROWS ( FILTER ( _t, [@logincount] >= 1 ) )

Percentage employee with 3 or more login: = 
VAR _employeecount =
    COUNTROWS ( VALUES ( Employee[Employee ID] ) )
VAR _t =
    ADDCOLUMNS (
        VALUES ( Employee[Employee ID] ),
        "@logincount", CALCULATE ( COUNTROWS ( Data ) )
    )
VAR _threeormore =
    COUNTROWS ( FILTER ( _t, [@logincount] >= 3 ) )
RETURN
    DIVIDE ( _threeormore, _employeecount )