Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Icon for Impactful Individual rankImpactful Individual
3 years ago
Solved

I need help to optimize Dax

Hi power BI community
I have a tabel that looks at diffrent states a task can come through todostaehistory
For each row I want to see if the same task todostaehistory [todoid] has been accepted at a later stage.
So my first line look make sure that we only look at the tasks that are offers todostatehistory[stateid] = 13.
So each row where this isn't the case we can just put a blank value.
The problem really come with VAR nextDate
Here each row look thorugh each other row where the stateid = 13 and l
and find the next row on the same task todostatehistory[todoid] made at a later point in time and with a diffrent todostatehistory[stateid].
 
Here is the DAX:

Accepted =
IF (
    todostatehistory[stateid] <> 13,
    BLANK (),
    VAR currentDate = Todostatehistory[date]
    VAR currentState = todostatehistory[stateid]

 

    VAR nextDate =
        SELECTCOLUMNS (
            CALCULATETABLE (
                TOPN ( 1, todostatehistory, Todostatehistory[id] ),
                ALLEXCEPT ( todostatehistory, todostatehistory[todoid] ),
                todostatehistory[stateid] <> currentState,
                Todostatehistory[date] > currentDate
            ),
            "@val", Todostatehistory[date]
        )
    RETURN
        nextDate
)

My problem is that each row looks though each other row to find the right date. This means that the memory required to update my report grows exponential as I get more rows and that is obviously unsustainable.
Does any of your bright folks have an idea to how I can get around this issue?
All help is greatly appreciated.
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    ThomasWeppler , try like

     

    new column =
    Maxx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] ) && Todostatehistory[date] > earlier(Todostatehistory[date]) ), Todostatehistory[date])

     

    Or break into two column
    or create a rank first - new columns
    rank = Rankx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] )), Todostatehistory[date],,asc,dense)


    new column date=
    Maxx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] ) && Todostatehistory[rank] > earlier(Todostatehistory[rank])+1 ), Todostatehistory[date])

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    ThomasWeppler , try like

     

    new column =
    Maxx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] ) && Todostatehistory[date] > earlier(Todostatehistory[date]) ), Todostatehistory[date])

     

    Or break into two column
    or create a rank first - new columns
    rank = Rankx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] )), Todostatehistory[date],,asc,dense)


    new column date=
    Maxx(filter(todostatehistory, todostatehistory[todoid] = earlier(todostatehistory[todoid] ) && Todostatehistory[rank] > earlier(Todostatehistory[rank])+1 ), Todostatehistory[date])