cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Why is my measure so slow?

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.

1 ACCEPTED SOLUTION
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
8 REPLIES 8
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper I

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.

Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper I

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

Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper I

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!

Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors