Forum Discussion

usernj123's avatar
usernj123
New Member
2 years ago
Solved

Max from related table

Hello All, I have 2 tables as shown below. Months table: (MonthName is text) TechSpend: MonthName      Text Category   Text Year          Text Actuals     Currency   TechSpend table ha...
Need Help
  • nsexton12's avatar
    nsexton12
    2 years ago

    I agree with the above comment about creating a proper date table! However, below is a solution anyway. 

    ----------------------------------------------------------------

    VAR FilteredVirtualTable =TOPN(1,FILTER(SUMMARIZE(Months,Months[MonthsName],Months[MonthNum]),Months[MonthsName] IN VALUES(TechSpend[MonthsName])),Months[MonthNum])

    VAR MonthNameVirtalTable = SELECTCOLUMNS(FilteredVirtualTable,"Month Name",Months[MonthsName])

    RETURN MonthNameVirtalTable 
    ----------------------------------------------------------------
     
     
    You needed to add one more step by creating a virtual table that is all matching rows between your months table and tech spend table, and then take the top row of that table after it is sorted by month number. The last step is using the selected columns function to grab the name of the month from that table. 
     
    Please mark as a solution if this worked for you!
     
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi usernj123 ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Total Actuals = 
VAR ActualExceptOther = ROUND(CALCULATE(sum(TechSpend[Actuals]), TechSpend[Category] <> "Other")/1000,2)
VAR _table= ADDCOLUMNS(ALL('TechSpend'),"monthnum",MONTH(CONVERT([MonthName]&" "&1,DATETIME)))
var MaxNum=MAXX(FILTER(_table,[Year] =SELECTEDVALUE('TechSpend'[Year])),[monthnum])
var  MaxMonth=CALCULATE(MAX('Months'[MonthName]),FILTER(ALL('Months'),'Months'[MonthNumber]=MaxNum))
RETURN MaxMonth & " - " & ActualExceptOther & "K"

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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