Dax Help : Status Column

[RENJITH_R_S]

Hi Friends, Could you please help me to write the dax query for the status column in the below example scenario

Week No is a slicer visual

Input Table
Name	Subject	Week No
A	CS	Week 1
B	IT	Week 1
C	EE	Week 1
A	CS	Week 2
S	CE	Week 2
F	EC	Week 2
Output Table
user selects date =  Week 1
Name	Subject	Status
A	CS	avaialble
B	IT	avaialble
C	EE	avaialble
user selects date = Week 2
Name	Subject	Status
A	CS	available
B	IT	left
C	EE	left
S	CE	available
F	EC	available

[v-luwang-msft]

Hi @RENJITH_R_S  ,

You could use the following steps:

Step1, create a new table like below:

Step 2，use the following dax to create a measure:

table =
VAR table1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Subject] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Week No] = SELECTEDVALUE ( slicertable[Week No] )
        )
    )
VAR table2 =
    IF ( MAX ( 'Table'[Subject] ) IN table1, "aliaviable", "left" )
RETURN
    table2

Then you will get the  below:

Wish  it is helpful for you!

Click   here  to download pbix if you need.

Best Regard

Lucien Wang

[RENJITH_R_S]

@v-luwang-msft 

I have worked on it, its doesn't met my requirement . Let me explain my requirement in a simpler way

Consider the data set(every week data will added up)

Name   Week No

Aju        week 31

Anu       week 31

Abu       week 31

binu       week 31

vinu       week 32

biju        week 32

viju         week 32

binu        week 32

ram         week 33

sam         week 33

viju          week 33

Now coming to visulization Week no is the slicer, once we select a value in slicer the selected week & its previous week needs to be considered

So if user selects week no = week 33

output is

Name   Status

ram       new joinee

sam      new joinee

viju        already

binu      left

vinu      left

biju      left

if user select week no = week 32

Aju        left

Anu       Left

Abu       left

binu       already

vinu       new joinee

biju        new joinee

viju        new joinee

So while selecting week number, first check the names of selected week with previous week, if name is matching then set "already", if name not found in previous week then set "New Joinee" , then check from previous week to selected week, if name is not found in selected week then set "left"

[RENJITH_R_S]

@v-luwang-msft Thanks 

[amitchandak]

@RENJITH_R_S , Create a new measure like

Measure =
var _1 = calculate(count([Subject]), filter(allselected(Table),Table[Subject] = max(Table[Subject])))
var _2 = calculate(count([Subject]), filter(all(Table),Table[Subject] = max(Table[Subject])))
return
if(Isblank(_1) && not(isblank(_2)), "left", "available")

[RENJITH_R_S]

@amitchandak , I have created your measure but its not working