Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mitchell92
Helper II
Helper II

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 🙂

 

1 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.

 

lbendlin_0-1708384153440.png

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 .

 

 

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

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...

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

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

lbendlin_0-1708134275639.png

 

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

 

Measure Simplification (Sample).pbix

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.

 

lbendlin_0-1708384153440.png

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 .

 

 

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:

 

BIEXAMPLE - 20.02.JPG

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

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

 

Thank you

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.