Forum Discussion

vgeldbr's avatar
vgeldbr
Helper IV
2 years ago

Poor Measure Performance and Confusion

I have simple data model shown below. The notable point is that the dimension table has 3.5m rows of data (a list of engagement codes).


I would like to generate a table visual that looks like below, with the first 3 columns coming from CTE YTD Excel Report and the highlighted 4th column coming from Engagement Code Full dimension table. The 5th column is a measure that I'm struggling with.

What I want from the measure is to return the first month of usage (ie. the minimum date in Usage Month for that specific person. The person is represented by a username and a GPN ID. 

 

I assumed the following simple measure would work:

FirstMonth = 
VAR User = SELECTEDVALUE('CTE YTD Excel Report (ServiceNow)'[GPN])
VAR Result = 
CALCULATE(
    MIN( 'CTE YTD Excel Report (ServiceNow)'[Usage Month] ),
    'CTE YTD Excel Report (ServiceNow)'[GPN] = User ),
    REMOVEFILTERS('CTE YTD Excel Report (ServiceNow))
    )
RETURN
Result

This works perfectly if I use the Engagement Code from the CTE YTD Excel Report but if I use the one from Engagement Code Master the performance becomes extremely slow (minutes to process).

 

I have tried various options for the measure including:

FirstMonth = 
VAR User = SELECTEDVALUE('CTE YTD Excel Report (ServiceNow)'[GPN])
VAR Result = 
CALCULATE(
    MIN( 'CTE YTD Excel Report (ServiceNow)'[Usage Month] ),
    KEEPFILTERS('CTE YTD Excel Report (ServiceNow)'[GPN] = User ),
    REMOVEFILTERS('Engagement Code Full'[ENGAGEMENT_NUM]),
    REMOVEFILTERS('CTE YTD Excel Report (ServiceNow)'[Usage Month])
    )

RETURN
Result

 

I feel I'm missing something very fundamental here and would appreciate some help or recommendations.

Need Help

14 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    Hey vgeldr,

    Maybe try:

     

    FirstMonth =
VAR Filtered =
CALCULATETABLE (
    VALUES ( 'CTE YTD Excel Report (ServiceNow)'[Usage Month] ),
    ALLEXCEPT ( 'CTE YTD Excel Report (ServiceNow)'[GPN] )
)
VAR Result =
MINX (
    Filtered,
    'CTE YTD Excel Report (ServiceNow)'[Usage Month]
)

RETURN
Result

     


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

    • vgeldbr's avatar
      vgeldbr
      Helper IV
      2 years ago

      Thanks Wilson_. This is still slow but more importantly doe snot produce the correct result:

       

      • Wilson_'s avatar
        Wilson_
        Memorable Member
        2 years ago

        vgeldbr,

         

        That definitely is a problem. I think I'd left the engagement number out of the ALLEXCEPT.

         

         

        FirstMonth =
VAR Filtered =
CALCULATETABLE (
    VALUES ( 'CTE YTD Excel Report (ServiceNow)'[Usage Month] ),
    ALLEXCEPT ( 'CTE YTD Excel Report (ServiceNow)'[GPN], 'Engagemenet Code Full'[ENGAGEMENT_NUM] )
)
VAR Result =
MINX (
    Filtered,
    'CTE YTD Excel Report (ServiceNow)'[Usage Month]
)

RETURN
Result

         

          

        If that is still not giving the right answer (I'm not always great at writing code in just my head hah) or faster, I would personally try pulling the user data out into its own dimension table and try something like:

         

        FirstMonth = MIN ( ALL ( 'CTE YTD Excel Report (ServiceNow)'[Usage Month] ) )

         


        ----------------------------------
        If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

  • vgeldbr's avatar
    vgeldbr
    Helper IV
    2 years ago

    I will have to look at the option but I still don't understand why adding the measure causes the issue in the first place since all of the relevant columns relate to the fact table. I don't understand what the DAX is doing and why it is simpacted by the large dimension table in the first place.