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 lik...
Need Help
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

        Wilson_ thanks again but not luck. Note that ALLEXCEPT requires Table and Column as parameters so you cannot include colums from two different tables. I tried adding two ALLEXCEPT (one for each table/column) but that did not work either.

         

        Your second option also does not work as MIN only accepts a column reference as an argument at ALL generates a table.