If condition based on measure

[Anonymous]

Hi All, 

I have a measure (1a) that calculates the no of hours that an employee has worked for the past 10 weeks.

I need another measure that flags that if they have worked more than 30 hours based on the above measure then flag as "1" on an employee level. 

ANd finally when I roll up to the department level, it will sum the total no of employees that have "1" i.e. worked more than 30 hours. 

I thought I could just write an if statement like: IF (1a. measure) > 30, then 1, 0) but I think you cannot have a measure as a condition... any suggestions would help!!!

Would be so grateful if someone could help. 

[selimovd · Accepted solution]

Hey @Anonymous ,

yes, that's possible. But you need a filter context for the measure, so you have to create a virtual table of all employees first in the measure. Then you can add the measure for the virtual table or check your criteria there directly and at the end summarize your result and return it.

The following approach should work for you:

Number employees with more than 10 hours =
-- Returns a distinct list of Employees
VAR vAllEmployees = VALUES( myTable[Employee] )
-- Then you add the measure to that table
VAR vAddMeasure =
    ADDCOLUMNS(
        vAllEmployees,
        "@MoreThan30Hours",
            IF(
                [measure hours that an employee has worked] > 30,
                1,
                0
            )
    )
RETURN
    -- At the end you can sum up the amount of employees who worked more than 30 hours
    SUMX(
        vAddMeasure,
        [@MoreThan30Hours]
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous · Accepted solution]

[Your Measure] =
countrows(
  filter(
    summarize(
      FactTable,
      Employee[EmployeeID]
    ),
    [1a. measure] > 30
  )
)

[selimovd · Accepted solution]

Hey @Anonymous ,

yes, that's possible. But you need a filter context for the measure, so you have to create a virtual table of all employees first in the measure. Then you can add the measure for the virtual table or check your criteria there directly and at the end summarize your result and return it.

The following approach should work for you:

Number employees with more than 10 hours =
-- Returns a distinct list of Employees
VAR vAllEmployees = VALUES( myTable[Employee] )
-- Then you add the measure to that table
VAR vAddMeasure =
    ADDCOLUMNS(
        vAllEmployees,
        "@MoreThan30Hours",
            IF(
                [measure hours that an employee has worked] > 30,
                1,
                0
            )
    )
RETURN
    -- At the end you can sum up the amount of employees who worked more than 30 hours
    SUMX(
        vAddMeasure,
        [@MoreThan30Hours]
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic