Return value of column, if certain criteria is met.

[Anonymous]

Hello everybody,

I have a table with lot's of process sequences listed up. 

Each process sequence is one separate line, with a different status, and different time stamp.

I want now to get one special timestamp, if it matches the criteria, and return the time stamp as new column (only if criteria is met). How can I do it? 

I tried like that, but it fails because of "no single value in table...":

Special time stamp = Calculate(MAX(PhaseInfo[DateTime],Filter(PhaseInfo,PhaseInfo[Operation_ID] = "Finishing" && PhaseInfo[Phase_ID] = "HAND" && PhaseInfo[Action_CD] = "226")))

-> Basically it should return the timestamp (DateTime), if those 3 criterias are matched in that single line (Finishing, Hand, 226).

Thanks in advance!

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.

Please try below steps:

1.below is my test table

PhaseInfo:

2. add a new column with below dax formula

Special time stamp =
CALCULATE (
    MAX ( PhaseInfo[Date Time] ),
    FILTER (
        PhaseInfo,
        PhaseInfo[Operation_ID] = "Finishing"
            && PhaseInfo[Phase_ID] = "HAND"
            && PhaseInfo[Action_CD] = 226
    )
)

Please refer the .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Binbin Yu,

this measure is working now. The only issue is, that it gives me the time stamp from a different batch.

So basically the table you have created, has for each time stamp also a batch number in relation.

What's now missing is, that it takes the "special time stamp" for that exact batch number, which is present in my table report. How can I do this now?

My table looks like this now:

The "special time stamp" is not from "OW2226". So what can I do now?

Thanks!

[v-binbinyu-msft]

Hi @Anonymous ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[amitchandak]

@Anonymous , other than one parenthesis position, the measures seem correct

Special time stamp = Calculate(MAX(PhaseInfo[DateTime]) ,Filter(PhaseInfo,PhaseInfo[Operation_ID] = "Finishing" && PhaseInfo[Phase_ID] = "HAND" && PhaseInfo[Action_CD] = "226"))