Need help with DAX logic

[Anonymous]

Hello Everyone, 

I need your help. 

I have a table with Students being out for certain days in a week as shown below:

I would like to create a DAX expression to get similar result.

If you have any idea pls share, thank you in advance. 

[v-mengzhu-msft · Accepted solution]

Hi @Anonymous ,

Like this?

You just need to create a cloumn:

Rate = var _count=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Student]=EARLIER('Table'[Student])))
return IF(_count=5,"High",IF(_count>1,"Medium",IF(_count=1,"Low")))

And then through conditonal formatting to get the color you want.

Refer my pbix file for better understanding.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , Do you have any date?

else try like

new column =

var _1 = countx(filter(Table, [Day] =earlier([Day]) && [Student] = earlier([Student]) ), [Student])

var _2 = countx(filter(Table, [Day] =earlier([Day]) && [Student] = earlier([Student]) && [Status] ="Out"), [Student])

Var _3 = distinctcount(Table[Day])

return

Switch(True() ,

_2 =_3, "High",

_2 =1, "Low",

"Medium"

)

[Anonymous]

Hey @amitchandak,

Thanks for the solution, it very close to what I was looking for.

I do have date column, where to use it if I want to see year to date (YTD) data? 

[v-mengzhu-msft]

Hi @Anonymous ，

Have you seen my reply? Can it meet your needs?

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly