Poor Measure Performance and Confusion

[vgeldbr]

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.

[vgeldbr]

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.

[Wilson_]

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]

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

[Wilson_]

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]

@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_]

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]

@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_]

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]

@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_]

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

[vgeldbr]

@Wilson_ you are correct. Engagement Code is unique in the Engagement Code Master table. It is not unique in the fact table.

So this leaves me with how to address this. Ultimately in my visual I need to show data from the Engagement Codes Master like the "type", "name", "owner". As soon as I use any value from that table then the First Month measure stops performing reasonably. 

I'm stuck...

[Wilson_]

vgeldbr,

Without knowing the contents of the lookup table or where the data is sourced or what you have permissions to do, the only thing I can suggest is you should find a way to filter down the number of records in your model. All I can tell with the information I have so far is over 95% of your dimension table doesn't actually have any rows in the fact table. Is it possible to go to the data source or to Power Query to filter out all the records in the dimension table that have no rows in the fact table?

[vgeldbr]

I responded a few days ago to my original post not the response from @Wilson_ .

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.

It's not feasible to do this but I'm hoping somebody can explain what I'm clearly missing in terms of data modelling and why using any column other than the key column from the Master Engagement Codes column would present this performance problem only when I use a measure. Simply adding additional columns to to my table visual from Engagement Master works just fine. And of course I'm still keen for any suggested ideas for how to address.

[v-xiandat-msft]

Hi @vgeldbr ,

@Wilson_ 's answer is very good , and I want to share other solutions:

Firstly:

Measures in DAX are dynamically calculated in response to user interactions and filters. When you add a metric value, it interacts with the entire data model.If your metric value references columns in both the fact data table and the dimension table, the impact on performance can be significant.DAX evaluates these relationships during query execution.The larger the dimension table, the more complex the calculations become, which affects query response time.

Please consider using DAX Profiler or other performance tools to identify bottlenecks and optimize the model.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.