Forum Discussion

Phoenix_Bird's avatar
Phoenix_Bird
Frequent Visitor
5 years ago
Solved

DAX Measure - ALL EXCEPT?

I need to write a DAX measure to return a value of "In" or "Out" for each employee based on the values in the Description fields of relevant rows.

 

Any employee that has a row for "Furlough Pay" needs to return the value "Out" (even if other rows for that employee have different descriptions).If an employee doesnt have rows with "Furlough Pay" then it should return the value "In".

 

So in the image below employees 12, 14, 16 & 43 would be "Out" but emloyees 22 & 55 would be "In".

 

Employee CategoryMeasure
12Basic HoursOut
12Furlough PayOut
12Sick PayOut
14Basic HoursOut
14Furlough PayOut
14Sick PayOut
16Basic HoursOut
16Furlough PayOut
16Sick PayOut
22Basic HoursIn
43Basic HoursOut
43Furlough PayOut
55Basic HoursIn

 

  • Hi, Phoenix_Bird 

    Please check the below for creating a new measure.

     

     

     

    New Measure =
    IF (
    "Furlough Pay"
    IN CALCULATETABLE (
    VALUES ( 'Table'[Category] ),
    ALLEXCEPT ( 'Table', 'Table'[Employee ] )
    ),
    "Out",
    "In"
    )
     
     
     
     

    Hi, My name is Jihwan Kim.


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


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

3 Replies

  • Hi, Phoenix_Bird 

    Please check the below for creating a new measure.

     

     

     

    New Measure =
    IF (
    "Furlough Pay"
    IN CALCULATETABLE (
    VALUES ( 'Table'[Category] ),
    ALLEXCEPT ( 'Table', 'Table'[Employee ] )
    ),
    "Out",
    "In"
    )
     
     
     
     

    Hi, My name is Jihwan Kim.


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


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

    Phoenix_Bird 

    Here is one way:

    1) Create a Dimension table for with the distinct values for Employee, and set up the model like this:

     

    2) Create the following two measures:

    Furlough =
    VAR Furlough =
        CALCULATETABLE (
            VALUES ( 'Dim Employee'[Employee ] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Category] = "Furlough Pay" )
        )
    VAR FRows =
        COUNTROWS ( INTERSECT ( VALUES ( 'Dim Employee'[Employee ] ), Furlough ) )
    VAR class =
        IF ( FRows = 1, "OUT", "IN" )
    RETURN
        class
    
    table rows = COUNTROWS('Table')

    Now create the table visual using the employee field from the dimension table, the category field from the main table, add the furlough measure and add the [table rows] measure as a filter for the visual in the filter pane setting the value to 1:

     

    I've attached the PBIX file for your reference

  • Phoenix_Bird's avatar
    Phoenix_Bird
    Frequent Visitor

    Great, thank you both for your help. I created it as a column rather than a measure.