cubedaxtabular

setting last child in dax tabular

Balance:=CALCULATE(SUM(Fact[Local]), 'select'[select Type]= "ACTION")

In Multidiemnsional model I could just select Aggregate Function as Last Child in the properties, but I what should do in the tabular in order to get the last child? Or If I have to write a dax formula what should I add from formula above?

Solution

  • Assuming you have a continuous date table, the equivalent in DAX to LastChild is

    CALCULATE ([Measure], LASTDATE(DateTable[Date]))

    If you don't have a continuous date table (i.e. you're using a degenerative date dimension, etc.) you'd use

    CALCULATE ([Measure], LASTNONBLANK(TableWithDate[Date], [Measure]))

    UPDATE: With DAX, it's almost always better to create intermediate measures to build up to your final measure for reuse, maintainability, debugging, etc.

    So first just use your existing Balance measure

    Balance:=CALCULATE(SUM(Fact[Local]), 'select'[select Type]= "ACTION")

    Then do 

    LastBalance:=CALCULATE([Balance], LASTNONBLANK(CalendarMonth[MonthKey], [Balance]) )