Forum Discussion

kl8818's avatar
kl8818
Frequent Visitor
3 years ago
Solved

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 GriswoldClarkGriswold[email protected]
4/1/2023123456789Clark GriswoldClarkGriswold[email protected]
4/1/2023123456789Clark GriswoldClarkGriswold[email protected]
4/1/2023123456789Clark GriswoldClarkGriswold[email protected]
4/1/2023123456789Clark GriswoldClarkGriswold[email protected]
5/1/2023123456789Clark GriswoldClarkGriswold[email protected]
5/1/2023987654321Uncle EddyUncle EddyJohnson[email protected]
5/1/2023987654321Uncle EddyUncle EddyJohnson[email protected]
4/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
4/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
4/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
4/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
4/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
4/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
5/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
5/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
5/1/2023563214897Ellen GriswoldEllenGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
4/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
4/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
4/1/2023789456132Rusty GriswoldRustyGriswold[email protected]
6/1/2023123456789Clark GriswoldClarkGriswold[email protected]
6/1/2023123456789Clark GriswoldClarkGriswold[email protected]
6/1/2023123456789Clark GriswoldClarkGriswold[email protected]
6/1/2023123456789Clark GriswoldClarkGriswold[email protected]
6/1/2023123456789Clark GriswoldClarkGriswold[email protected]
6/1/2023123456789Clark GriswoldClarkGriswold[email protected]
4/1/2023654789213Audry GriswoldAudryGriswold[email protected]

 

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's avatar
    Jihwan_Kim
    3 years ago

    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 )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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 )