Forum Discussion

buttercream's avatar
buttercream
Icon for Helper II rankHelper II
6 months ago
Solved

Need help creating a measure

Hello,

 

My data looks like this:

 

EmployeeGroupDateLeave
112Finance1/7/20251
112Finance1/8/20251
112Finance1/11/20251
112Finance1/12/20251
112Finance2/10/20251
113IT1/4/20251
113IT2/1/20251
114IT1/2/20251
114IT1/3/20251
114IT1/4/20251

 

I need a measure to show me the number of employees in the finance group that took leave more than 3 times.  I'm going to use this measure in a visual that breaks out by month so expected result is 1 employee for January.

 

I tried the below but it's incorrect:

CALCULATE(DISTINCTCOUNT(Table[Employee]),FILTER(Table,Table[Group]="Finance"),FILTER(Table,CALCULATE(SUM(Table[Leave]))>3))
Need Help
  • buttercream's avatar
    buttercream
    6 months ago

    My apologies but I'm trying to calculate it by month.  Example below.

     

    EmployeeGroupDateLeave
    101Finance2/12/20251
    101Finance2/13/20251
    101Finance2/14/20251
    101Finance2/15/20251
    101Finance2/16/20251
    101Finance3/20/20251
    101Finance3/21/20251
    101Finance3/22/20251
    107IT1/22/20251
    107IT1/23/20251
    107IT1/24/20251
    107IT1/25/20251
    107IT2/3/20251
    107IT2/4/20251
    108IT2/1/20251
    108IT2/2/20251
    108IT2/3/20251
    108IT2/4/20251
    112Finance1/7/20251
    112Finance1/8/20251
    112Finance1/11/20251
    112Finance1/12/20251
    112Finance2/10/20251
    113IT1/4/20251
    113IT2/1/20251
    114IT1/2/20251
    114IT1/3/20251
    114IT1/4/20251
    120Finance1/2/20251
    120Finance1/4/20251
    120Finance2/1/20251
    120Finance2/3/20251
    132Finance1/2/20251
    132Finance1/4/20251
    132Finance1/5/20251
    132Finance1/6/20251
    132Finance2/3/20251
    132Finance2/4/20251
    132Finance2/5/20251
    132Finance3/2/20251
    132Finance3/3/20251
    132Finance3/4/20251

     

    I should see 2 employees for January and 1 for February for a total of 3 as highlighted in red above.

     

    Here is the result from your measure:

     

     

  • techies's avatar
    techies
    6 months ago

    Hi buttercream here ISINSCOPE handles the total row correctly by summing the monthly values instead of recalculating across all months. Sharing the PBIX file for reference.

     

     

  • hnguy71's avatar
    hnguy71
    6 months ago

    buttercream 

    Assuming you have a period column the following DAX expression should give you what you're looking for:

    Finance Emp 3+ Leaves = 

VAR _Group = "FINANCE"

VAR _Base = 
SUMMARIZE( 
        CALCULATETABLE(
            'Table',
             'Table'[Group] = _Group
        ),
    [Employee], [Period]
)

VAR _CheckLeave =
FILTER(
    ADDCOLUMNS(
        _Base,
        "Leave", 
        VAR _Emp = [Employee]
        VAR _Period = [Period]
        RETURN
        CALCULATE(SUM('Table'[Leave]), 'Table'[Employee] = _Emp, 'Table'[Period] = _Period)
    ), 
    [Leave] > 3
)

RETURN

COUNTROWS(_CheckLeave)

     

    I've attached a sample pbix for you.

14 Replies

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    6 months ago

    Hi buttercream please try this

     

    Finance Employees With 3+ Leaves = VAR EmpTable =
        ADDCOLUMNS(
            VALUES(LeaveData[Employee]),
            "LeaveCount",
                CALCULATE(
                    SUM(LeaveData[Leave]),
                    LeaveData[Group] = "Finance"
                )
        )
    RETURN
    COUNTROWS(
        FILTER(EmpTable, [LeaveCount] > 3)
    )
     
     
     
    • buttercream's avatar
      buttercream
      Icon for Helper II rankHelper II
      6 months ago

      Thanks.  Almost works.  The measure is ignoring the LeaveData[Group] = "Finance" and instead giving me the count for all groups.

      • techies's avatar
        techies
        Icon for Super User rankSuper User
        6 months ago

        Hi buttercream please try this

         

        Finance Employees With 3+ Leaves = VAR FinanceEmployees =
            FILTER(
                VALUES(LeaveData[Employee]),
                CALCULATE(COUNTROWS(LeaveData), LeaveData[Group] = "Finance") > 0
            )
        VAR EmpTable =
            ADDCOLUMNS(
                FinanceEmployees,
                "LeaveCount",
                    CALCULATE(
                        SUM(LeaveData[Leave]),
                        LeaveData[Group] = "Finance"
                    )
            )
        RETURN
        COUNTROWS(
            FILTER(EmpTable, [LeaveCount] > 3)
        )
         
         
         
    • buttercream's avatar
      buttercream
      Icon for Helper II rankHelper II
      6 months ago

      Thanks.  This is working but how do I add finance group only to the measure?

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    6 months ago

    Please try the logic below:

    Measure =
VAR EmpSummary =
SUMMARIZE(
FILTER( 'Table', 'Table'[Group] = "Finance" ),
'Table'[Employee],
"LeaveCount", SUM( 'Table'[Leave] )
)
RETURN
COUNTROWS( FILTER( EmpSummary, [LeaveCount] > 3 ) )
  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    6 months ago

    buttercream 

    Assuming you have a period column the following DAX expression should give you what you're looking for:

    Finance Emp 3+ Leaves = 

VAR _Group = "FINANCE"

VAR _Base = 
SUMMARIZE( 
        CALCULATETABLE(
            'Table',
             'Table'[Group] = _Group
        ),
    [Employee], [Period]
)

VAR _CheckLeave =
FILTER(
    ADDCOLUMNS(
        _Base,
        "Leave", 
        VAR _Emp = [Employee]
        VAR _Period = [Period]
        RETURN
        CALCULATE(SUM('Table'[Leave]), 'Table'[Employee] = _Emp, 'Table'[Period] = _Period)
    ), 
    [Leave] > 3
)

RETURN

COUNTROWS(_CheckLeave)

     

    I've attached a sample pbix for you.