Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Roy_tap
Helper I
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
amitchandak
Super User
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)

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi, @Roy_tap 

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

 

Picture6.png

 

 

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

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.


Go to My LinkedIn Page


amitchandak
Super User
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)

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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