Forum Discussion

JMG241188's avatar
JMG241188
Icon for Helper I rankHelper I
3 years ago
Solved

Use CALCULATETABLE to return latest version

Hey,

 

I thought I solved my problem but it is still not summarising the way I want.

 

In breif, I have a table with a contract idea, a status, and a version number, and I want the table to always show the most recent version, even when I filter out data.

 

The table below is the raw data;

Contract IDStatusVersion Number 
1BOUND1
1PARTIALLY_BOUND2
1PARTIALLY_BOUND3
1PARTIALLY_BOUND4
1PARTIALLY_BOUND5
1NEW6
2BOUND1
2BOUND2
2BOUND3
2DRAFT4

 

This is how I expect it to read, with no filters on, returning the MAX of the version number;

Contract IDStatusVersion Number 
1NEW6
2DRAFT4

 

 

If, however, I was to remove "DRAFT" on a filter, I would expect the following table;

Contract IDStatusVersion Number 
1NEW6
2BOUND3

 

I used the following DAX, and it works but only when filtered to one Contract ID.

 

Any help would be great appreciated;

 

IsLatestVersion =
VAR t =
CALCULATETABLE(
ADDCOLUMNS(
    SUMMARIZE(
       Contracts,
       Contracts[Master Contract Version Id]
    ),
    "@id",
    CALCULATE(LASTNONBLANKVALUE(Contracts[Version Number], MAX(Contracts[Version Number]))),
    "@date",
    CALCULATE(LASTNONBLANKVALUE(Contracts[Version Number], MAX(Contracts[Version Number])))

), ALLSELECTED())

RETURN

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

3 Replies

    • JMG241188's avatar
      JMG241188
      Icon for Helper I rankHelper I
      3 years ago

      Hey amitchandak , I have an issue with the formula above, it worked in certain visuals, but not for others.

       

      I ended up with the following formula;

       

      LatestVersion = var _max = maxx(filter(ALLSELECTED(Contracts), Contracts[Master Contract Version Id] = Max(Contracts[Master Contract Version Id])), Contracts[Version Number])
      return
      CALCULATE(max(Contracts[Version Number]), filter((Contracts) ,  Contracts[Version Number] = _max))
       
      This is OK to use as a Is Not Blank filter on tables where each contract is separated, but it doesn't work when the data is aggregated.
       
      What I need to do is return the $ limit of each of the latest versions, summed up together. I tried to change what the formula was returning, and again, it works at line level, but when aggregating contracts together, it only returns one value. Which is likely expected with the DAX measure, but I need to know how to take all of these limits, and sum them together.
       
      LatestVersionLimit = var _max = maxx(filter(ALLSELECTED(Contracts), Contracts[Master Contract Version Id] = Max(Contracts[Master Contract Version Id])), Contracts[Version Number])
      return
      CALCULATE(SUM('Contract Sections'[Overall Limit]), filter((Contracts) ,  Contracts[Version Number] = _max))
       
      Thanks so much,
       
      J