Forum Discussion
create column data from multiple rows
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. 😟
Mine is a calculated column formula. The EARLIER() function works only in a calculated column formula.
8 Replies
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.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.
I am getting an error when trying to use the SRU Tier or SRU RTO code.
Am I doing this wrong?
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.
Hi,
Write this calculated column formula
SRU Tier = CALCULATE(MIN(Data[Tier]),FILTER(Data,Data[SRU Name]=EARLIER(Data[SRU Name])))
