Why is my measure so slow?

[scorbin]

I have a measure that counts distinct employee names based on another measure that calculates the percentage of billed hours from total hours. This is to count the total employees that have a certain billed hours percentage. I want to view this count measure in a matrix with the columns using dates (week number) and the rows are job titles. Essentially seeing how many employees were a certain billed hours percentage that week. 

My count measure looks like this: 

Over 20% Billable = 
CALCULATE(
      DISTINCTCOUNT(Table[fullname]),
      FILTER(Table, Table[Billable Percent] > .2)
) 

The Billable Percent measure is fairly simple and looks like this:

Billable Percent = 
 VAR billable = SUM(Table[billablehours])
 VAR totalhours= SUM(Table[totalhours])
 RETURN
 DIVIDE(billable, totalhours)

Right now, using the count measure broken down by date takes a really long time to load anything. Several minutes at least, and this of course happens any time I make any changes to the visual. Am I doing something wrong here? Is there a more effecient way to build this measure or get these results? 

I would also like to eventually compare this number to the total employee count. 

[edhans]

Hi @scorbin ,

The problem could be the table you are filtering. You are filtering the entire table, and if that table is huge, your measure will be slow. 

Try this:

Over 20% Billable =
CALCULATE(
    DISTINCTCOUNT( Table[fullname] ),
    Table[Billable Percent] > .2
)

THat is equivalent to this longer filter:

Over 20% Billable =
CALCULATE(
    DISTINCTCOUNT( Table[fullname] ),
    FILTER(
        ALL( Table[Billable Percent] ),
        Table[Billable Percent] > .2
    )
)

The difference between both of these and your original filter is these only filter the distinct values of the Billable Percent field where it is > .2. Those values will then be used to filter the fullname filed and return your distinct count. 

[scorbin]

I am getting an error saying that "A function 'PLACEHOLDER' has been used in a True/False expression that is used in the Table Filter expression. This is not allowed." when I use your suggested measure. 

[edhans]

Not sure why. Can you show us your measure?

Just used this measure and it works fine in my Contoso sample report.

There was some work done in 2021 to simplify some of the filters in CALCULATE. What version of Power BI Desktop to you have?

If it is older, use the long form of the filter above with the FILTER(ALL()) combo. Or upgrade to a newer version.

Otherwise, I'd need to see some data and see what is in the table.

[scorbin]

Or can I not use a measure within the ALL() section? The [Billable Percent] is a measure. 

[edhans · Accepted solution]

Ahhh... you didn't say that. Never use a table name with the measure. 

If you just want the full name, then try this:

Over 20% Billable =
CALCULATE(
    DISTINCTCOUNT( Table[Fullname] ),
    FILTER(
        ALL( Table[Fullname] ),
        [Billable Percent] > .2
    )
)

Beyond that, we'd need to see the model, know the table size, how many distinct values are in the full name field, etc.

[scorbin]

Oh, ok. I was not aware that you don't use the table name with the measure. But that seems to have worked and is much faster. Thanks! 

[edhans]

Great. Glad I was able to thelp @scorbin 

When you are searching for code examples the standard practice is always include the table name for a column - TableName[Column Name], but never include it for a measure - [Total Sales]. 

DAX does not enforce this. You can skip column names in calculated columns and table, and the code works, and you can add table names to measures, and the code works. But it makes it hard for humans to read.

ANd I am glad it is going faster. The reason is your table might be quite large, and you were filtering that entire table. But the VALUES(TableName[Column]) is only one column, and only the unique values, so a fraction of the size, < 1% of the table size most likely, so it runs much faster.

[scorbin]

Seems like I have the version last updated in September 2021. 

I (think) I did what was suggested with the new measure: 

Over 20% Billable = 
CALCULATE(
      DISTINCTCOUNT(Table[fullname]),
      Table[Billable Percent] > .2
) 

I'll give the FilterAll combo a shot.