Forum Discussion

Mitchell92's avatar
Mitchell92
Icon for Helper II rankHelper II
2 years ago
Solved

Inefficient DAX Formula - need assistance please

Hi all,

 

Just hoping to receive some assistance relating to a DAX formula that is causing a "Visual has exceeded the available resources" issue (seemingly due to inefficient formula).

 

In short, what is happening is I have two tables - one matrix which is the "summary" table, and one table that is the "detail" table.

 

The summary table has a list of names, and for this example, an average "contact time" and average "booking time". The detail table again has a list of names, further table details (i.e. create date, job no. etc) and the contact time and booking time. The issue I am facing is that when I filter a name on the summary table, the contact time (or booking time) does not load if that individiual only contacted or booked one of the events (i.e. person A contacted, person B booked). It simply returns a blank in one or the other column.. This is kind of expected, although I would like the time to show regardless.

 

The way I though about getting around this was removing the filter on the name if the result is a blank. This actually seemed to work for a split second, and then I received the error relating to available resources. This makes me believe the formula is quite inefficient - hoping to receive some tips!

 

The formula in question (contact time):

 

if(
CALCULATE(AVERAGE(CONLINE[CONTACTEDTIME]), USERELATIONSHIP(CONLINE[EVENTREGUSER1100], EMPMASA[EMF_USERID])) = blank(),
CALCULATE(AVERAGE(CONLINE[CONTACTEDTIME]), USERELATIONSHIP(CONLINE[EVENTREGUSER1100], EMPMASA[EMF_USERID]), removefilters(EMPMASA[FULL NAME])),
CALCULATE(AVERAGE(CONLINE[CONTACTEDTIME]), USERELATIONSHIP(CONLINE[EVENTREGUSER1100], EMPMASA[EMF_USERID]))
)

 

Thank you in advance 🙂

 

"DAX help"
Average from Matrix
  • lbendlin's avatar
    lbendlin
    2 years ago
    3.1 TIMETOCONTACT =
COALESCE (
    CALCULATE (
        AVERAGE ( CONLINE2[CONTACTEDTIME] ),
        USERELATIONSHIP ( CONLINE2[EVENTREGUSER1100], EMPMASA2[EMF_USERID] )
    ),
    CALCULATE (
        AVERAGE ( CONLINE2[CONTACTEDTIME] ),
        USERELATIONSHIP ( CONLINE2[EVENTREGUSER1100], EMPMASA2[EMF_USERID] ),
        REMOVEFILTERS ( EMPMASA2[FULL NAME] )
    )
)

    Not sure what else is going on in your data model but the relationships should be single directional.

     

    Give it a whirl and see if this is any better. Note that I didn't touch 4.1 so you can compare.

     

    Next step would be to fire up DAX Studio and test the queries against the actual data .

     

     

9 Replies