Max custom column

[Bron_B_T]

Hi 

I have a table which I need to flag the maximum record within a dataset, I then want to filter these records out and make a relationship between this table (which would then only have unique values) with another table which has the ponum listed muliple times.  

The table of data has the following type structure:

ponum	revisionnum	Concatenate	LatestRecordFlag
287	0	2870	FALSE
287	1	2871	FALSE
287	2	2872	TRUE
10922	0	109220	FALSE
10922	1	109221	FALSE
10922	2	109222	TRUE
11405	0	114050	FALSE
11405	1	114051	FALSE
11405	2	114052	TRUE
25207	0	252070	TRUE
27137	0	271370	TRUE
31139	0	311390	TRUE
32305	0	323050	TRUE

I can achieve doing a flag within the model stage but I need to take it back to the custom column because I still can't make the tables link.  The formula I am using to flag is working:

LatestRecordFlag = 'Purchase Orders'[Concatenate] =
CALCULATE(MAX('Purchase Orders'[Concatenate]),
FILTER(ALL('Purchase Orders'), 'Purchase Orders'[ponum]=EARLIER('Purchase Orders'[ponum])))

I need to convert this formula to use in the query editor or to the 'dax' language. The concatenate column is in the query editior already.

Please help, thank you for anyones assistance. 

[Anonymous · Accepted solution]

Ok I have a way to solve this for you.  We will do all of this in Power Query.

Step 1:  Create a new query, which you will name "fnGetLatestRecord".  Use this code (paste into Advanced Editor)

(table as table, ponum as text) => 
   try Table.Max(Table.SelectRows(table, each [ponum]=ponum),"revisionnum")[revisionnum] otherwise null

Step 2: Make sure you have your original table, which i have labeled as "ExampleTable"

Step 3: Create a new Query, which i have called "LatestTable" but you can call it whatever you like.  Use the following code:

let
    Source = ExampleTable,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ponum", type text}}),
    #"Add LatestRecord" = Table.AddColumn(#"Changed Type", "LatestRecord", each fnGetLatestRecord(#"Changed Type", [ponum])),
    #"Added Custom" = Table.AddColumn(#"Add LatestRecord", "LatestRecordFlag", each [revisionnum] = [LatestRecord]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([LatestRecordFlag] = true))
in
    #"Filtered Rows"

Now all you need to do is rename "ExampleTable" in the step 3 code, to your real table name.

Optionally you can set your original table (ExampleTable in mine) to not be EnableLoad.  This will hide it in PowerBI

[Bron_B_T]

Hi there

Thank you very much for taking the time to respond and to provide a solution, I've been able to apply this to my dashboard and now have the unique values and the tables linked the way I wanted.

Really appreciate this!

Bron_B_T