Forum Discussion
Responsive ResidentGrouping Names using department name + Months between 2 dates involving 2 columns
If you look at my proposal you will notice the " || " in the code - that is DAX speak for "OR", meaning my proposal already implemented the logic you want to achieve.
Super UserThe first question is ambiguous. What have you tried and where are you stuck?
The second question can be answered with DAX. Do you want this as a measure or is a calculated column sufficient?
romovaroHi Ibendin
For the first question I created a different excel file (Table) with the names I want to see in the report and created a relationship with the column Taskactualperformer. (that has all the names working on the process). The report needs to show only the KPI from some of them. (pre Engagement team) I would like to create a column saysing....if this name; then Pre Engagement Team , if not....else.
Tasks IPM HandOff & Closed = VAR result =
CALCULATE (
DISTINCTCOUNT( CELERGO_12062021V1[CID] ),
FILTER (
CELERGO_12062021V1,
CELERGO_12062021V1[TASK]
IN { "Pre Engagement Process" , "Pre Engagement Process" , "IPM HandOff" }
&& CELERGO_12062021V1[TASK_STATUS] IN {"Received", "closed", "Received"}
)
)
RETURN
IF ( ISBLANK ( result ), 0, result )
Regarding the formula above...What I need to show is all the tasks that have
Task= Pre Engagement Process and Task_Status="Closed or Received" and
Task = IPM Hand Off and Task_Status= "Received"
with this formula I get 127. (But in the excel file I have 127 Pre engagement process closed/Received" and 18 IPM HandOff received....somethin's missing.
and then, once formula is fixed tHe idea is to add...and only for the "Pre-engagement team"...
2) At the end, what managament wants to see if the number of tasks
How many Task = Pre Engagement Process were task_Status="Received" first day of the month and How many Task=pre Engagement process were Task_Status = closed last day of the month.Calculated column or value is ok- lbendlin
1.
Tasks IPM HandOff & Closed = 0+CALCULATE ( DISTINCTCOUNT( CELERGO_12062021V1[CID] ), CELERGO_12062021V1[TASK] = "Pre Engagement Process" && CELERGO_12062021V1[TASK_STATUS] IN {"closed", "Received"} || CELERGO_12062021V1[TASK] = "IPM Hand Off" && CELERGO_12062021V1[TASK_STATUS] = "Received" )- romovaro
Hi Ibendin
thanks. I my excel file i have 106 Pre engagement process (closed or Received) and 4 IPM HandOff with status Received....
Just noticed that CID is the same in Pre engagement process than IPM handOff, therefore distinctcount cannot be used.
I used the formula below with your help only using COUNT...but i get the same number 106 (still missing the 4 IPM i guess...)
Maybe the correct formula is using the "OR" instead of &&?
Task= Pre Engagement Process and Task_Status="Closed or Received" OR
Task = IPM Hand Off and Task_Status= "Received"
COuld you update the formula using or requiremtn 1 "Task= Pre Engagement Process and Task_Status="Closed or Received" " or Requirement 2 "Task = IPM Hand Off and Task_Status= "Received""...please?all task = 0+CALCULATE (COUNT( 'Celergo-PreEngTeam'[CID] ),'Celergo-PreEngTeam'[TASK] = "Pre Engagement Process" && 'Celergo-PreEngTeam'[TASK_STATUS] IN {"closed", "Received"}OR'Celergo-PreEngTeam'[TASK] = "IPM Hand Off" && 'Celergo-PreEngTeam'[TASK_STATUS] = "Received")CLIENT_NAME CID INSTANCE_NUMBER TASK TASK2 TASK_ASSIGNED_ON TASK_ASSIGNED_ON2 TASK_STATUS Client A 380310 1122324 Start Partner Engagement - 1107560 - 380310ES01 Start Partner Engagement - 1107560 - 380310ES01 23-NOV-21 05.45.08.000000 PM 23-NOV-21 Closed Client A 380310 1107560 IPM HandOff IPM HandOff 18-NOV-21 01.06.40.000000 PM 18-NOV-21 Received Client A 380310 1122323 Start Partner Engagement - 1107560 - 380310CA01 Start Partner Engagement - 1107560 - 380310CA01 18-NOV-21 01.06.40.000000 PM 18-NOV-21 Closed Pacaso 380310 1107560 Pre Engagement Process Pre Engagement Process 01-OCT-21 02.52.09.000000 PM 01-OCT-21 Closed