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

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.

 

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    vgeldbr,

     

    Sure, should be easy enough fix on the second to get around the issue. Something like:

    MINX ( 
    ALL ( 'CTE YTD Excel Report (ServiceNow)'[Usage Month] ),
    'CTE YTD Excel Report (ServiceNow)'[Usage Month]
)

     

    Keep in mind, I'm just typing syntax without an editor so feel free to modify if I've made any simple syntax errors. 🙂


    ----------------------------------
    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_ This still produces the result above (ie. wrong with the user showns for each and every one of the 2.3m engagement codes).

       

      • Wilson_'s avatar
        Wilson_
        Memorable Member
        2 years ago

        vgeldbr,

         

        First of all, I got so annoyed with all the errors in my DAX that I produced my own small sample dataset so I could get my measure correct at least. I was able to but in looking at your question again, I noticed something I glossed over earlier that may be a much more productive path forward on your issue.

         

        Why does your dimension table have 24x more records than your fact table? The inverse seems much more standard. I'm not sure what the data source on your engagement codes is but can you filter out all the codes that aren't in your fact table upstream (either in the data source itself or in Power Query in your Power BI semantic model)?