Totally stuck on producing a slider with a distinct count of a text column

[Anonymous]

Hi there

I have a table showing total records assigned to an employee.

There is a column holding a unique id [Record ID] and a columne holding employee name called [Employee Name]

It looks like this:

Record ID   Employee Name

1001           Bob Sagget

1002           Christina Aguliera

1003           Reg Varney

1004           Bob Sagget

I can show total records by employee, by doing a DISTINCTCOUNT('Table'[Record ID]) and show it against [Employee Name] in a table. Based on the above sample it would look like this:

Employee Name    Total Records

Bob Saget              2

Christina Aguliera  1

Reg Varney             1

In real life, the total records per employee range from 7 to 2000 with an average of about 400 records per employee.

I want to have a slider for this 'total records' by Employee, so my users can remove outliers like 7 and 2000 if they want, and just look at the middle values.

Totally stumped on how to achieve this. 

HELP!

[BA_Pete]

Hi @Anonymous ,

In Power Query, I would duplicate your first table then group by [employeeName] and add an aggregation column that is just Count Rows.

Once you load this to the data model, you can use your count column in a slicer and it should default to a 'Between' slicer using contiguous integers between your min and max count values.

You should then be able to relate this count table to other tables on [employeeName] (not recommended due to possible duplicates, but that's the example you've given) in order to filter them based on your slicer.

Pete

[AlexisOlson · Accepted solution]

You could create a calculated column with total records per employee and then use that column for your slicer.

Total Records Calculated Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Record ID] ),
    ALLEXCEPT ( 'Table', 'Table'[Employee Name] )
)

[Anonymous]

Thanks both @AlexisOlson and @BA_Pete both viable solutions.

I went with Alexis calculated column because I don't need to create a separate table and relationship and it's working exactly as I wanted!

Thanks so much you're a GENIOUS 😄 

[BA_Pete]

Hi @Anonymous ,

I agree, @AlexisOlson 's solution is quicker and easier to implement and, if you have few records and endusers with decent computers, his is the superior solution.

However, if your endusers complain about slow load and/or response times, or resource limit errors, then please try my solution.

Alexis's solution will require enduser resources to calculate and materialise your column at runtime, mine will offload this processing to your gateway during refresh so minimal runtime processing.

In fact, if you are using an SQL server or similar as a source, Power Query will fold this basic grouping to the server, so will be exceptionally fast to materialise.

Pete

[AlexisOlson]

@Anonymous  Yeah, mine is a quick-and-easy solution that's not best practice (especially for larger models). For smaller models, there shouldn't be any noticeable performance hit, especially to end users since calculated columns are only computed at data refresh and not in response to slicers/filters in the report.

However, if you notice this causing a slow down in refresh time (or just prefer being conscientious about following best practices), you should definitely move the calculation upstream to Power Query as @BA_Pete suggests.