Create slicer of distinct values in a single column

[ericOnline]

I'm not sure why this is so non-intuitive...

Sample Data:

Fname	Lname	JobTitle
Bob	Smith	Carpenter
Sally	Jones	Carpenter
Yvonne	Brinkley	Blacksmith

Goal:

- Create a DAX Measure that defines unique JobTitles

- Add a slicer of unique Job Titles to dashboard

Tried so far:

- This should be as easy as creating the following measure...

UNIQUE_JOBTITLES = DISTINCT('SAMPLE_DATA'[JobTitle])

- ...or slightly less intuitively...

UNIQUE_JOBTITLES = VALUES('SAMPLE_DATA'[JobTitle])

 - ...then applying the measure to the Slicer visual.

Which mystic formulae am I missing here?

Thank you

[v-robertq-msft · Accepted solution]

Hi, @ericOnline 

I think the first thing you should keep in mind is that the goal of creating a measure is to calculate the value based on the DAX formula. And measure can't be placed in Slicer. So I think maybe creating measures is not a good solution. To get the distinct value of the column [JobTitle], I think creating a calculated table is the best way to achieve this, you can try my steps:

1. Create a calculated table:

Table 2 =

SUMMARIZE('Table','Table'[JobTitle])

2. Create a relationship between the two tables:

3. Create a Slicer and place ‘Table 2’[JobTitle], you can get what you want, like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[AllisonKennedy]

@ericOnline  You can't use a measure to create a slicer - only columns can be used for slicers, axis, legend, etc. As @Fowmy mentioned, you don't need to create any formula for this to work. If you do need a table of distinct values, create a new TABLE not a new MEASURE. Power BI Order of Operations 

[Fowmy]

@ericOnline 

You do not need a formula, drag and drop the Job Title Column to a slicer, it will show you the distinct values.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn