cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Assigning of new status value based on another field condition

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

1 ACCEPTED SOLUTION
Super User

@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)

2 REPLIES 2
Super User

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.

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

Super User

@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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors