Count Help

kl8818 — Thu, 06 Jul 2023 18:52:03 GMT

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

Goal -

Card Visual to show count of how many employees w/ 1 or more logins
Card Visual to show percentage of employees w/ 3 or more logins
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.

Re: Count Help

Jihwan_Kim — Fri, 07 Jul 2023 03:17:06 GMT

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 )

topic Count Help in DAX Commands and Tips

Count Help

Re: Count Help