Forum Discussion
Post PartisanPie Chart Problems - Can't Add Values for Totals Using Last Date Only
Hi, julesdude ;
You could create a measure.
total = var _max=CALCULATE(MAX('TableA'[Date]),ALLEXCEPT(TableA,'TableA'[Country])) return CALCULATE(SUM('TableB'[Total Value]),FILTER(TableB,[Date]=_max))The final show:
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.
Community ChampionHi julesdude
I used two calculated columns to achieve the below however I am confident this can be achieved more efficiently in some other way. Output below:
Two calculated columns are:
1. Max Date to return the latest date based on the country:
Max Date =
VAR _1 = 'Table (2)'[Country]
VAR _2 = MAXX ( FILTER ( ALL ('Table (2)' ) , 'Table (2)'[Country] = _1 ) , 'Table (2)'[Last Updated] )
RETURN
_2
2. Max Amount to return the maximum amount based on parameters specified:
Max Amount =
VAR _1 = CALCULATE ( MAX ('Table (2)'[Total Value] ) , ALLEXCEPT ( 'Table (2)' , 'Table (2)'[Country] ,'Table (2)'[Asset Reference] ,'Table (2)'[Last Updated] ) )
VAR _2 = IF ( AND ( _1 = 'Table (2)'[Total Value] , 'Table (2)'[Last Updated] = 'Table (2)'[Max Date] ) , _1 , 0 )
RETURN
_2
I hope this helps 🙂
Theo
Post PartisanTheoC thank you I am very grateful for your help.
I think your solution might be difficult to implement for added DAX columns because in my example I did simplify things somewhat into one table. Technically I have two tables at work here (although they do have a relationship) which are joined by asset reference:
So if we are going to go the add column using DAX route, it might need to be modified?
Like you, I was thinking there could be a cleaner way of achieving this, but I'm happy to go with whatever works if the extra columns route is easier.
Any help appreciated.