Calling the Max Version for a given category

[FayeB1901]

Hi All 

I am trying to call a value relating to the Maximum version number for a category in a separate table. 

I've managed to create a table which shows me the Max version number for the category, now I need to force my visual to show me the result that relates to that max version. Any ideas how I can do that? 

More info:
There are 3 separate tables, for simplicity they are: 
1. Version ID - multiple for each category 
2. Highest Version ID (by Category)
3. Value - i.e. the value I want to call based on the highest version ID, matched to the Version ID.

Any help appreciated as always!

Faye

[v-yalanwu-msft]

Hi, @FayeB1901 ;

Is the above answer helpful to you? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

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

[v-yalanwu-msft · Accepted solution]

Hi, @FayeB1901 ;

Could you share the scene or simply pbix about your data after removing sensitive information? base on my understand , you could create a new table as follows:

1.original data.

2.create a new table.

newtable =
SELECTCOLUMNS (
    FILTER (
        'STANDARDJOBTABLE',
        [VERSIONID]
            = CALCULATE (
                MAX ( 'STANDARDJOBTABLE'[VERSIONID] ),
                ALLEXCEPT ( 'STANDARDJOBTABLE', 'STANDARDJOBTABLE'[STANDARDJOBID] )
            )
    ),
    "group", [STANDARDJOBID],
    "highest version", [VERSIONID],
    "value", [ Value]
)

The final output is shown below:

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

[amitchandak]

@FayeB1901 , Create a combined key category and version id and join two table and get max category

Or you can try like

calculate(lastnonblankvalues(Table[Version ID], Max(Table[Value])), allexcept(Table[Category]))

[FayeB1901]

Thanks for the suggestion but neither seem to work.

I can't create a key from my Max Version field as it's from a calculated table: 

Expected result = SELECTCOLUMNS(FILTER(STANDARDJOBTABLE, STANDARDJOBTABLE[VERSIONID]=SVCSTANDARDJOBTABLE[Max Version by SJ]),"Group",STANDARDJOBTABLE[STANDARDJOBID],"highest version",STANDARDJOBTABLE[Max Version by SJ])