Assigning of new status value based on another field condition

[Roy_tap]

Hi All,

I have the following tables with the following sample columns which i would like to compute an addition Status Column:

Project  Type

A           Client

B           Rep

C           Client

C           Rep

D           Client

D           Rep 

E            Client

E            Client

F            Rep

When the Project is only based on Client, it will be assign with 1(Project A and E), if it is based on Rep, it will be assign with 2(Project B and F) and if it is based on both Client and Rep, it will be assign with 3(Project C and D). Sometimes, the project could be based on 2 client (Project E) or even 2 rep which will be assign with 1 and 2. I would like to achieve the status column but i am having difficulty of creating the dax coumn based on my condition stated above.

Project  Type      Status

A           Client     1

B           Rep        2

C           Client     3

C           Rep        3

D           Client     3

D           Rep        3

E            Client     1

E            Client     1

F            Rep        2 

Any suggestion on how i could achieve this status measure. Thanks

[Jihwan_Kim]

Hi, @Roy_tap 

Please check the below picture and the formula for creating a new column.

Status CC =
VAR currentproject = 'Table'[Project]
VAR currenttype =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Project] = currentproject ),
'Table'[Type]
)
RETURN
IF (
"Client"
IN currenttype
&& COUNTROWS ( currenttype ) = 1,
1,
IF ( "Rep" IN currenttype && COUNTROWS ( currenttype ) = 1, 2, 3 )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak · Accepted solution]

@Roy_tap , Create a new column like

new column =
var _1= countx(filter(Table, [Project] = earlier([Project]) && [Type] ="Client"),[Project])+0
var _2= countx(filter(Table, [Project] = earlier([Project]) && [Type] ="Rep"),[Project])+0
return
SWitch(true() ,
[_1] >0 && [_2] >0 , 3,
[_2] >0 ,2,
1)