Multiple conditions that have to be true at the same time on same column from a Table

[Anonymous]

Hi All,

I have a table JobStatus, with PipelineId,Status.

PipelineId	Status
1	Build
1	Unit Test
1	Functional Test
1	Scan Status
2	Build
2	Unit Test
2	Functional Test
2	Scan Status

I am trying to get diffrent levels based on the status for each pipelineid. PipelineId is not unique.

if (Status = "Build" && Status = "Unit Test" && Status = "Functional Test" && Status = "Scan Status" Then "Level 0)

Above statement is not working when I use && (and) condition.

Not sure how to add pipelineId as filter to the above statement.

Expected result is 

PipelineId	Status	Levels
1	Build	Level 0
1	Unit Test	Level 0
1	Functional Test	Level 0
1	Scan Status	Level 0
2	Build	Level1
2	Unit Test	Level1
2	Functional Test	Level1
2	Scan Status	Level1

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Rank =
RANKX(
    FILTER(ALL('Table'),
    'Table'[PipelineId]=EARLIER('Table'[PipelineId])),[Index],,ASC)

Levels =
var _build=CALCULATE(MAX('Table'[Status]),FILTER(ALL('Table'),
'Table'[PipelineId]=EARLIER('Table'[PipelineId])&&'Table'[Index]=EARLIER('Table'[Index])))
return
"Levels"&" "&
IF(
   AND( 'Table'[Rank]=1 ,'Table'[Status]="Build") ||
   AND( 'Table'[Rank]=2 ,'Table'[Status]="Unit Test")||
AND( 'Table'[Rank]=3 ,'Table'[Status]="Functional Test")||
AND( 'Table'[Rank]=4 ,'Table'[Status]="Scan Status"),
'Table'[PipelineId]-1
)

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but based on how the expected outcome looks like, I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new column.

Levels CC = 
"Level " & JobStatus[PipelineId]

[Anonymous]

Hi Jihwan,

PipelineId are alphanumeric and your code did not worked. I have diffrent status for different pipelineids.

pipelineIds are nothing but URLs. We can not append the ids. I have given you just a sample to understand the requirement. Thank you

[amitchandak]

@Anonymous , Try a new column like

new column=
Var _cnt = calculate(distinctcount(Table[Status]), filter(Table, Table[PipelineID] = earlier(Table[PipelineID) && [Status] in {"Build" ,"Unit Test" , "Functional Test" , "Scan Status" }))
return
if(_cnt =4, "Level 0", "Level 1")

[Anonymous]

Hi Amit,
I am getting only 2 status as Level 0 and Level1. I should get all the status based on the available status for pipelineids. Please see below new column changes and results 

Results