Forum Discussion
Dax Help : Status Column
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 |
5 Replies
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")amitchandak , I have created your measure but its not working
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
table2Then you will get the below:
Wish it is helpful for you!
Click here to download pbix if you need.
Best Regard
Lucien Wang
v-luwang-msft Thanks
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"
