topic Re: Inefficient DAX Formula - need assistance please in DAX Commands and Tips

Inefficient DAX Formula - need assistance please

Mitchell92 — Fri, 16 Feb 2024 01:04:00 GMT

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 🙂

Re: Inefficient DAX Formula - need assistance please

lbendlin — Fri, 16 Feb 2024 02:46:22 GMT

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-Forum/ba-p/963216

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/1447523

Re: Inefficient DAX Formula - need assistance please

Mitchell92 — Fri, 16 Feb 2024 03:51:30 GMT

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

Re: Inefficient DAX Formula - need assistance please

lbendlin — Sat, 17 Feb 2024 01:44:42 GMT

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

Re: Inefficient DAX Formula - need assistance please

Mitchell92 — Mon, 19 Feb 2024 22:45:10 GMT

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

Measure Simplification (Sample).pbix

Re: Inefficient DAX Formula - need assistance please

lbendlin — Mon, 19 Feb 2024 23:12:05 GMT

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 .

Re: Inefficient DAX Formula - need assistance please

Mitchell92 — Mon, 19 Feb 2024 23:37:26 GMT

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:

Re: Inefficient DAX Formula - need assistance please

lbendlin — Mon, 19 Feb 2024 23:48:46 GMT

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

Re: Inefficient DAX Formula - need assistance please

Mitchell92 — Tue, 20 Feb 2024 00:33:50 GMT

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

Thank you

Re: Inefficient DAX Formula - need assistance please

lbendlin — Tue, 20 Feb 2024 01:58:37 GMT

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