If row equal to X and does not equal to Y in different row then Z per category

[vojtechsima]

Hello, guys,

I have probably a simple ask, but I need help to figure out the best-optimized way how to handle this.

It can be either a measure or a calculated column.

I want to COUNT rows that have to evaluate for "Declined Offer".

Here's the data and outcome:

ID	Status	IsDeclined
1	Hired	FALSE
1	Offered	FALSE
1	Onboarding	FALSE
2	Offered	TRUE
2	Interviewed	TRUE
3	Interviewed	FALSE

Logic:

If status = Offered and status <> Onboarding or Hired, then I want TRUE, otherwise FALSE.

Please note, every row is only one status and I want to count distinct IDs.

Thanks for any help.

[AlexisOlson · Accepted solution]

Here's another method for a measure:

DeclinedCount =
VAR _IDs_ =
    FILTER (
        DISTINCT ( Table1[ID] ),
        VAR _StatusList_ = CALCULATETABLE ( VALUES ( Table1[Status] ) )
        VAR _Offered     = "Offered"    IN _StatusList_
        VAR _Onboarding  = "Onboarding" IN _StatusList_
        VAR _Hired       = "Hired"      IN _StatusList_
        RETURN
            _Offered && NOT (_Onboarding || _Hired )
    )
RETURN
    COUNTROWS ( _IDs_ )

[vojtechsima]

Hi, @AlexisOlson 
I have issues with your measure, it says it's not returning Table.

I copy pasted your code and adjust my setup so it's the same table and columns, but I have issues with the "IN" function it seems.
And frankly, I understand the code, but I don't understand the 

 IN _StatusList_

part.

[AlexisOlson]

Sorry. It should be CALCULATETABLE rather than CALCULATE (I've corrected it above).

_StatusList_ is simply the list of status values corresponding to that particular ID. The IN function is syntactic sugar for CONTAINSROW.

[vojtechsima]

This work, thank you,
You're truly a Super User @AlexisOlson 

[bolfri]

DAX for you
Create new column in your table:

IsDeclined = 
var _statusNeeded = {"Offered"}
var _statusExcluders = {"Onboarding";"Hired"}
var _currentID = [ID]
var _correctStatus = CALCULATE(COUNTROWS('Sample');'Sample'[ID]=_currentID;'Sample'[Status] in _statusNeeded)
var _wrongStatus = CALCULATE(COUNTROWS('Sample');'Sample'[ID]=_currentID;'Sample'[Status] in _statusExcluders)
return AND(_correctStatus>0;_wrongStatus=0)

Measure to calculate unique ID for true records:

Measure = CALCULATE(DISTINCTCOUNT('Sample'[ID]);'Sample'[IsDeclined])

[vojtechsima]

Hey @bolfri 

FK_JOB_APPLICATION	Application Status	IsDeclinedFlag
1	Interview1	False
1	Onboarding	False
1	Hired	False
1	Default	False
1	Decline	False
1	Offerextended	True
1	Offeraccepted	False
1	Prepare Offer	False
1	Workerscouncilreview	False
1	Interview4	False
1	Interview2	False
1	Interview3	False
1	Interviewedpendingdecision	False
1	Interview5	False
1	Assessment	False
1	Preofferbkgrdcheck	False
1	Proceed	False
1	Schedule Interview	False
1	Shortlisted	False
1	Easy Apply (Don'T Touch)	False
1	Postofferbkgrdcheck	False

IsDeclinedFlag = 
var _statusNeeded = {"Offerextended"}
var _statusExcluders = {"Onboarding", "Hired", "Onboardingint"}
var _currentID = 'Applications Status'[FK_JOB_APPLICATION]
var _correctStatus = CALCULATE(COUNTROWS('Applications Status'),'Applications Status'[FK_JOB_APPLICATION]=_currentID,'Applications Status'[Application Status] in _statusNeeded)
var _wrongStatus = CALCULATE(COUNTROWS('Applications Status'),'Applications Status'[FK_JOB_APPLICATION]=_currentID,'Applications Status'[Application Status] in _statusExcluders)
return AND(_correctStatus>0,_wrongStatus=0)

Doesn'T seem working, it should be all false.