I have a table as displayed below, where I need a DAX Measure or two Custom columns that will give me the MAX Date and the Column Name it came from. I want to create a value that will give me the Max Date and Column name in the Row.
I can create a Custom Column to get the Max Date, but I need to also return the column name in another column.
List.Max({[Stage 1 Date],[Stage 2 Date],[Stage 3 Date],[Stage 4 Date],[Stage 5 Date],[Stage 6 Date],[Stage 7 Date]})
Ok this is an interesting one because I wouldn’t solve this just with DAX.
I think you need to complete some work in the query editor first here.
You basically want to unpivot this table, or atleast these columns so that they move to just two columns.
One column will be Stage Number and the Other will be the results.
By doing this you can also get rid of all the null in this table.
Then your DAX measure will be very simple.
This is exactly how I would solve this quite quickly.
Let me know how you go.
Add the file here if no progress.
Chrs
Hi Sam, I can unpivot the table no issues, but then I only want to keep one row for each and remove the rest from the Table, this is the challenge I have.
Any ideas
Thanks
Nev
Hi Neville,
Sorry I’ve read you comment a few times and not really understanding it too well.
When you unpivot, you should have two columns of information. One for the stage and then one for the date.
From here you have many options to run calculations using DAX measures. This is what I would provide.
What sort of calculations are you after? For example what context are you using in your tables or visuals.
Once a table is setup in an unpivoted way I’m confident finding the max date for anything should be super easy.
Would just be as simple as writing MAX( Date Column ) regardless of the context of the calculation actually.
Chrs
Sam
Hi Sam, I know I could filter the DAX measures based on MAX date, but I would have to do this for every DAX calculation, as I only want to keep the row in the table with the MAX date as I’m not interested in any of the other rows as all the columns are exactly the same.
Hope this is clear now.
Thanks
Nev
Hi Nev,
Sorry I’m just not getting the naunces to this scenario.
I’m probably going to have to see more with either a file, or more detailed images of what you’re looking at.
In my mind, I’m imagining this is quite simple, but it doesn’t seem that way as we’re not getting what you need.
Chrs
Hi Sam,
There are 20 columns in the table and I need the Column Name and value of the MAX date to be represent in a two columns, 1 Column called “Stage Name” and the Other with “Stage Date”. If I unpivot the columns named Stage 1 Date to Stage 7 Date, I will end up with multiple rows containing the other 13 colums where there was multiple dates is the columns Stage 1 to Stage 7 Date. I only require one row.
In the example table I provided, if I unpivot, I will end up with 3 rows.
Row #; Stage Name; Stage Date; Other Columns in Table.
9;Stage 3 Date;03/10/2016; Other Columns from same Row in Table
9;Stage 4 Date;28/06/2016; Other Columns from same Row in Table
9;Stage 5 Date;19/10/2016; Other Columns from same Row in Table
In the above example, I only wish two Keep the first Row as that’s the one with the MAX date and I also have the Stage name (Stage 3) that contained the Max Date.
In the other columns I have as Total Value for that row, so if I end up with three rows, then the total value will be tripled. I want to avoid have to filter all the rows in the DAX Measures just to include the one row with the MAX date, which is why I just was to delete all the other rows from the Table.
Maybe this is not as simple as I thought it would be?
Thanks
Nev
Ok I think I know where the disconnect here is.
What you need to do is create 2 tables here, 2 fact tables.
One would be with all the detailed data as is now, then you want to break out the Stage information into another tables and unpivot that. So you will just have the staging information in one single table.
You then need to create a lookup table which can link the two and create the correct filtering that you require.
What would be an appropriate index that could work across these two tables and basically become the link between them as a lookup table?
I’m pretty confident this is what you need now.
I’ve worked with this scenario one time from memory but was for a client so I don’t have a specific example for you.
Is this making sense, solving this in the model…
Hi @sam.mckay , I never got this to work how I want, so going to try and explain again here as I have a similar use case.
In the Custom Column, I get the MAX date from the selected columns in the row context. I need a new column that will give me the Column Name, and if there are two dates of the same value, it will return the earliest Column Name.
Take Row 3 with Opportunity ID of 3. The MAX List Date = 6th July 2020, but as this is matching in both Columns “Stage 1” and “Stage 2” the results should be given as Stage 1.
Take Row 2 with Opportunity ID of 2. The MAX List Date = 7th October 2021, but as this is match in both Columns “Stage 3” and “Stage 5” the result should be given as Stage 3
Take Row 1 with an Opportunity ID of 1. The MAX List Date = 8th March 2020, and as there is only one date matching the MAX List Date, the result will be give as Stage 4
Hope you or anyone can help.
Latest Stage.pbix (78.6 KB)
Thank you
Nev
Hi @Neville,
In future please create a new topic and just reference the old one if that’s required for additional context. See if this meets your requirement. Just paste the code into a new blank query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7RDQAhCEN34dukgnq6i3H/Nc7Gw3AJH69QCnOKSpJbAwWWLVOtNMU2KCp79DU0xw7Nzj/JtbI7D7pHBfyKpnp4HwzDjnFTaWrhNV7wb9d6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opportunity ID" = _t, #"Stage 1" = _t, #"Stage 2" = _t, #"Stage 3" = _t, #"Stage 4" = _t, #"Stage 5" = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Opportunity ID", Int64.Type}, {"Stage 1", type date}, {"Stage 2", type date}, {"Stage 3", type date}, {"Stage 4", type date}, {"Stage 5", type date}}),
GetMax = Table.AddColumn(ChType, "Custom", each List.Max( Record.ToList( Record.RemoveFields( _, "Opportunity ID" )))),
GetName = Table.AddColumn(GetMax, "ColName", each Record.FieldNames(_){List.PositionOf(Record.FieldValues(_), [Custom])})
in
GetName
I hope this is helpful
@Melissa , this works great, but in reality I have around 20 columns in my table, so how should I update this to only include columns starting with "Stage " as a Column Name
Hi @Melissa , yes but only what to do this on column names of Stage 1, Stage 2, Stage 3, etc…
Thanks
Nev
Hi @Neville,
Okay, give this a go instead. Just paste the full script into a new blank query.
I’ve added a ListStages variable that identifies the Column-/Fieldnames starting with “Stage”
let
ListStages = List.Select( Table.ColumnNames(Source), each Text.StartsWith( _, "Stage", Comparer.OrdinalIgnoreCase )),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7RDQAhCEN34dukgnq6i3H/Nc7Gw3AJH69QCnOKSpJbAwWWLVOtNMU2KCp79DU0xw7Nzj/JtbI7D7pHBfyKpnp4HwzDjnFTaWrhNV7wb9d6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opportunity ID" = _t, #"Stage 1" = _t, #"Stage 2" = _t, #"Stage 3" = _t, #"Stage 4" = _t, #"Stage 5" = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Opportunity ID", Int64.Type}, {"Stage 1", type date}, {"Stage 2", type date}, {"Stage 3", type date}, {"Stage 4", type date}, {"Stage 5", type date}}),
GetMax = Table.AddColumn(ChType, "Custom", each List.Max( Record.ToList( Record.SelectFields( _, ListStages )))),
GetName = Table.AddColumn(GetMax, "ColName", each Record.FieldNames(Record.SelectFields( _, ListStages )){List.PositionOf( Record.ToList( Record.SelectFields( _, ListStages )), [Custom])})
in
GetName
I hope this is helpful
Was able to integrate this into my current table, and is exactly what I need.
This is also great, as I’ve learnt some more M today.
Thanks
Nev