Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
kl8818
Frequent Visitor

Count Help

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:

DateEmployee IDUser NameUser First NameUser Last NameUser Email
4/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
4/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
4/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
4/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
4/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
5/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
5/1/2023987654321Uncle EddyUncle EddyJohnsonuncleeddy@christmasvacation.com
5/1/2023987654321Uncle EddyUncle EddyJohnsonuncleeddy@christmasvacation.com
4/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
4/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
4/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
4/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
4/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
4/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
5/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
5/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
5/1/2023563214897Ellen GriswoldEllenGriswoldellengriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
4/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
4/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
4/1/2023789456132Rusty GriswoldRustyGriswoldrustygriswold@christmasvacation.com
6/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
6/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
6/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
6/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
6/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
6/1/2023123456789Clark GriswoldClarkGriswoldclarkgriswold@christmasvacation.com
4/1/2023654789213Audry GriswoldAudryGriswoldaudrygriswold@christmasvacation.com

 

ATTEMPT 1 ---

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

kl8818_0-1688668128636.png

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:
kl8818_1-1688668737045.png

 

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'

kl8818_3-1688668912423.png

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

kl8818_2-1688668852115.png

 

 ATTEMPT 3 ---

 Adjustments made in power query: Created pivot table
kl8818_4-1688668988967.png
 
kl8818_5-1688669353953.png

 

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. 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

Jihwan_Kim_1-1688699765809.png

 

 

Jihwan_Kim_0-1688699753006.png

 

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 )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

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.

Jihwan_Kim_1-1688699765809.png

 

 

Jihwan_Kim_0-1688699753006.png

 

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 )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors