How to dynamically filter table to return most recent version

[JMG241188]

Hello,

I am trying to solve a table filtering problem I have. And have a tried a few methods.

I have tried creating a DAX measure that ranks contracts on the version number and and using this as a filter, but this requires the contract id to be included in the visual.

I have also tried to create an index in the query editor to mark the most recent version as "1", which is working until I apply filters to the data.

What I want to do is apply filters to the status of the contract, ie, to remove "drafts" for instance, and then the data dynamically finds the next most recent version and returns that instead.

I envisage this will have to be some sort of dynamic DAX measure that relies on the CALCULATETABLE function, but finding it difficult to know where to start.

Happy to give more detail but not sure about what detail to give.

thanks

[JMG241188 · Accepted solution]

I found the solution;

IsLatestVersion =

VAR t =

CALCULATETABLE(

ADDCOLUMNS(

    SUMMARIZE(

       Contracts,

       Contracts[Contract Id]

    ),

    "@id",

    CALCULATE(LASTNONBLANKVALUE(Contracts[Created At], MAX(Contracts[Version Number]))),

    "@date",

    CALCULATE(LASTNONBLANKVALUE(Contracts[Created At], MAX(Contracts[Created At])))

), ALLSELECTED())

RETURN

COUNTROWS(FILTER(t, [@id] = SELECTEDVALUE(Contracts[Version Number])))

[ichavarria]

Hi @JMG241188! It seems like you are trying to filter a table to only show the most recent version of each contract while also being able to filter by contract status. One approach you can try is to use a combination of DAX measures and Power Query steps to achieve this.

Here's one possible solution:

1. In the Power Query Editor, create a new column called "IsLatestVersion" that uses the following formula:
   
   = if [Version] = List.Max(List.Select([Versions], each [Status] <> "Draft")).[Version] then true else false
   
   This formula checks if the current row's version is the same as the highest version number for that contract where the status is not "Draft". The result is a boolean value that is true for the most recent version of each contract.
   
   

2. Load the table into the data model and create a DAX measure that counts the number of contracts where "IsLatestVersion" is true:
   
   LatestContractsCount = COUNTROWS(FILTER(Contracts, [IsLatestVersion] = true))
   
   This measure will count the number of contracts that have the "IsLatestVersion" column set to true.
   
   
3. Create a slicer for the contract status column and apply it to the visual. Then, create a DAX measure that filters the table by the selected status and the "IsLatestVersion" column:
   
   LatestContracts = CALCULATETABLE(Contracts, FILTER(Contracts, [IsLatestVersion] = true && [Status] = SELECTEDVALUE('Contract Status'[Status])))
   
   This measure uses the CALCULATETABLE function to filter the "Contracts" table by the selected status and the "IsLatestVersion" column.
   

With these steps, you should be able to filter the table to only show the most recent version of each contract while also being able to filter by contract status. Let me know if you have any questions or if this solution doesn't meet your needs.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accept it as the solution and give Kudos to help the other members find it more quickly.

[JMG241188]

Hey @ichavarria , thanks for the prompt and detailed response, I think you are probably right, I'm just struggling to implement your solution.

I have gone into the Query editor, and tried to create a custom column as the following;

if [Version Number] = List.Max(List.Select([Version Number], each [Status] <> "Draft")) then true else false

I am getting the following error;

Expression.Error: We cannot convert the value 4 to type List.
Details:
Value=4
Type=[Type]

4 in this instance is the version number in the table.

It might be worth pointing out here also that "Draft" isn't the only Status I want to be able to restrict, so it would be good to remove this from this formula likely, as sometimes I will want to see the latest draft version.

Thanks,

J

[ichavarria]

Hi @JMG241188,

To handle multiple statuses, you can modify the formula to include a parameter that specifies the status to include. For example:

= if [Version Number] = List.Max(List.Select(Contracts, [Contract ID] = [Contract ID] and [Status] = statusParameter)).[Version Number] then true else false

You can then pass the desired status as a parameter when creating the measure that filters the table.

Let me know if this helps or if you have any further questions!


Best regards, 

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

[JMG241188]

I've updated the original post to hopefully make it clearer. Ultimately, in the table example I shared, if I was looking at Contract ID 2, Version 4 would be the most recent version in this case. I want to be able to filter out the status "Draft", and then for it to return to me, Version Number 3 as that would now be the most recent. Hopefully that's clearer.

[JMG241188]

Thanks again for the super fast reply, I really appreciate your help, I have created a test version of the database, it won't let me add the formula in the query editor, it is giving me the following error;

"Token "then" expected."

I've pasted some test data below, I don't know how to upload the PowerBI test file I just created, but this is what I'm working with.

Contract IDStatusVersion Number

1	BOUND	1
1	PARTIALLY_BOUND	2
1	PARTIALLY_BOUND	3
1	PARTIALLY_BOUND	4
1	PARTIALLY_BOUND	5
1	NEW	6
2	BOUND	1
2	BOUND	2
2	BOUND	3
2	DRAFT	4