cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem
Post Prodigy
Post Prodigy

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

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!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

Hi @heytherejem ,

 

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
Super User
Super User

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

View solution in original post

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 😄 

Hi @heytherejem ,

 

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

@heytherejem  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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.