create column data from multiple rows

[jstanley1017]

I have a dataset that consists of many applications that are grouped by what we call an SRU Name. (many to one relationship between App ID and SRU Name). I would like to create an SRU Tier, SRU RTO and SRU Resilience based upon the applications that are associated with each SRU Name. For example for SRU Name=S1, SRU Tier=1 (need the min value), SRU RTO=<15 minutes (need min value), Resilience=AP (where we take the lowest value from AA, AS, AP). I am seeking to have the columns in red calculated (if possible)

App ID	Tier	RTO	Resilience	SRU Name	SRU Tier	SRU RTO	SRU Resilience
A1	2	<30 minutes	AA	S1	1	<15 minutes	AP
A2	3	<4 hours	AP	S1	1	<15 minutes	AP
A3	1	<15 minutes	AS	S1	1	<15 minutes	AP
A4	4	<8 hours	AP	S1	1	<15 minutes	AP
A5	4	<8 hours	AP	S2	1	<15 minutes	AP
A6	1	<30 minutes	RR	S2	1	<15 minutes	AP
A7	5	<72 hours	AS	S3	2	<30 minutes	AS
A8	4	<8 hours	AS	S3	2	<30 minutes	AS
A9	2	<30 minutes	AS	S3	2	<30 minutes	AS
A10	2	<30 minutes	AA	S3	2	<30 minutes	AS

Is there a way to code for something like this?

I am happy to add the notebook, but I am not sure how. 😟

[Ashish_Mathur]

Hi,

Write this calculated column formula

SRU Tier = CALCULATE(MIN(Data[Tier]),FILTER(Data,Data[SRU Name]=EARLIER(Data[SRU Name])))

[Irwan]

hello @jstanley1017 

please check if this accomodate your need.

1. since your 'RTO' might be in text form, you need to change this into number so you can get time order for 'SRU RTO'. there are plenty way to do this, but i did this in the simplest way. i used PQ to split number and time unit to get 'Time Duration' in minutes.

2. 'SRU Tier', i assumed you want to get the lowest 'Tier' for same 'SRU Name'

SRU Tier = 
MINX(
    FILTER(
        'Table',
        'Table'[SRU Name]=EARLIER('Table'[SRU Name])
    ),
    'Table'[Tier]
)

3. 'SRU RTO', i assumed you want to get the lowest 'RTO' category for same 'SRU Tier'

SRU RTO =
MINX(
    FILTER(
        'Table',
        'Table'[SRU Tier]=EARLIER('Table'[SRU Tier])
    ),
    'Table'[RTO]
)

since 'RTO' might be in text form, you most likely can not do MINX directly so you need to create another calculation for 'Time Duration' as i mentioned above.

'SRU RTO' basically seeks the lowest 'Time Duration' on every same 'SRU Tier' then returns as 'RTO' value (text form).

then for 'SRU Resilience', i dont know how to determine AP or AS.

you mentioned in your post, where we take the lowest value from AA, AS, AP. But what value to get the lowest?

Hope this will help.
Thank you.

[jstanley1017]

I am getting an error when trying to use the SRU Tier or SRU RTO code.

Am I doing this wrong?

[Irwan]

hello @jstanley1017 

the code is for DAX, not PQ.

after you have your data in PBI, then create a calculated column and paste the DAX.

also, if you have custom sort for Resilience, then it might be better to do indexing by RANK or RANKX depend on what value you have in custom sort.

Hope this will help.

Thank you.

[jstanley1017]

I am getting an error from the EARLIER function.

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

[Irwan]

hello @jstanley1017 

as @Ashish_Mathur mentioned, EARLIER works only in calculated column. You got that error because you put EARLIER in measure not calculated column.

create a calculated column for 'SRU Tier' and 'SRU RTO' then use either my DAX or @Ashish_Mathur 's DAX with changes based on your table and column name (either DAX should be fine depend on your preferences DAX).

Thank you.

[Ashish_Mathur · Accepted solution]

Mine is a calculated column formula.  The EARLIER() function works only in a calculated column formula.

[jstanley1017]

Thanks Irwan! For the last part, we have a custom sort with Resilience sorted from high AA (high), AS, AP (low). I am guess I would have to assign values to these for sorting purposes.