Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JMG241188
Frequent Visitor

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])))
1 ACCEPTED SOLUTION
3 REPLIES 3
JMG241188
Frequent Visitor

@amitchandak you are a legend! Thank you so much!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.