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" n...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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
5 years ago

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