Inefficient DAX Formula - need assistance please

[Mitchell92]

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 🙂

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Mitchell92]

Hi @lbendlin ,

Pbix file located here: Measure Simplification (Sample).pbix

It is working in this document due to the heavily stripped back dataset. Hence why I think a simpler measure will be the solution.

Thank you

[lbendlin]

The highlighted columns are calculated columns, not measures.  Please clarify.

[Mitchell92]

Apologies @lbendlin , please see the below .pbix file with the correct measures located in the detail table:

Measure Simplification (Sample).pbix

[lbendlin · Accepted solution]

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 .

[Mitchell92]

Hi @lbendlin , thank you. This formula definitely is more efficient - it is actually able to load!

The problem I am facing now is that the Detail table doesn't filter per the user summary selection. Ideally i'd like the Detail table to only show results of specifically selected users in the Summary table. I understand this is likely happening due to the inclusion of both filtered and non-filtered results... so wondering if there is any crafty way around this?

Example below of issue:

[lbendlin]

You made the relationships inactive, so you have to carry that filter yourself, via TREATAS.

[Mitchell92]

Hi @lbendlin , would you happen to be able to provide a DAX example for this case?

Thank you

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.