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

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
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 .

 

 

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

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

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

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

       

      Thank you

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

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