DAX Measure - ALL EXCEPT?

[Phoenix_Bird]

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	Category	Measure
12	Basic Hours	Out
12	Furlough Pay	Out
12	Sick Pay	Out
14	Basic Hours	Out
14	Furlough Pay	Out
14	Sick Pay	Out
16	Basic Hours	Out
16	Furlough Pay	Out
16	Sick Pay	Out
22	Basic Hours	In
43	Basic Hours	Out
43	Furlough Pay	Out
55	Basic Hours	In

[Phoenix_Bird]

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

[PaulDBrown]

@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

[Jihwan_Kim · Accepted solution]

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"

)

https://www.dropbox.com/s/r9h4l3haeujm7ie/pheonix.pbix?dl=0 

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