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,

 

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)?

    • vgeldbr's avatar
      vgeldbr
      Helper IV
      2 years ago

      Wilson_ , the dimension table is a list of financial codes. Each code has many attributes that are required in the report (e.g. status, description, owner). I will need these. I'm focused though just on the immediate problem is just using the engagement code (which is the key for the relationship) causes the performance to grind to a crawl.

      • Wilson_'s avatar
        Wilson_
        Memorable Member
        2 years ago

        vgeldbr,

         

        In my humble opinion, that is part of the immediate problem. When you're switching from using the engagement code from the fact table to using it from the dimension table instead, Power BI is not just using a field with 24x more rows, it is likely using a field with many times more than that if you only consider unique values (I'm guessing it's unique in your dimension table and not in your fact table).