Create calculated column based on another column result

[Rayzo92]

Hello dear members , 

I have a table called planning that contain the absence details of my employees . 

I wanted to create a calculated column that contain the absence details of my employee but in case an employee have 2 absence reasons in the same day  i want the colmumn to have the value Multi .

I did some research and i notice that i might be able to do that with all expect funtion , but here i have 2 condition (same ID and same Day)

Here is a small sample ! what im trying to acheive (Created column in green 😞 

thanks in advance all 

[Rayzo92]

Hello , 

@Bifinity_75,  @serpiva64  thanks for your answers .

your both solution are working but there is only 1 condition that have issue , is that when the employee is absent the same day with the same reason it shouldnt show multi since it's the same reason of absence .

[serpiva64]

Hi,

try this

Column1 = var currid = 'Table'[ID]

var currdate = 'Table'[Date]

var result = CALCULATE(DISTINCTCOUNT('Table'[Reason]),FILTER('Table','Table'[ID]=currid && 'Table'[Date]=currdate))

return

if (result=2,"Multi", 'Table'[Reason])

[Rayzo92]

@serpiva64  , unfortunately the query dont want to charge . it keep loading only , do you think it's related to the query performance ? 

[serpiva64]

Hi,

Can you post your calculated column?

[Rayzo92]

M1 = var currid = Planning[Matricule]

var currdate = Planning[Date Planning]

var result = CALCULATE(DISTINCTCOUNT(Planning[Measure planning_theorique] ),FILTER(Planning,Planning[Matricule]=currid && Planning[Date Planning]=currdate))

return

if (result=2,"Multi", Planning[Measure planning_theorique])

this is my calculation column dax 

[Bifinity_75]

Hi @Rayzo92 , try this calculate column:

Created Column = IF(
CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[id],'Table'[day]))>1,
"Multiple",'Table'[reason of absence])

Best regards

[serpiva64 · Accepted solution]

Hi,

with Dax you can add a calculate column

Column = var currid = 'Table'[ID]

var currdate = 'Table'[Date]

var result = countrows(FILTER('Table','Table'[ID]=currid && 'Table'[Date]=currdate))

return

if (result=2,"Multi", 'Table'[Reason])

If this post is useful to help you to solve your issue consider giving the post a thumbs up  and accepting it as a solution !