Forum Discussion

pfarahani's avatar
pfarahani
Helper II
5 years ago

How to Determine Unique Values in Table

Hello All,   I am trying to figure out a way to determine the unique values in this column. They are a list of departments. Can I write a measure?  Once I get a list of the codes that are in this c...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

pfarahani either the VALUES() or DISTINCT() DAX functions will return the unique values from a column, with slight differences.  See documentation linked below.

However, note that you can't display the result of VALUES() or DISTINCT() directly in a visual - rather you typically use them as an intermediate step inside a measure calculation or calculated table. 

I would need more information regarding how you're intending to use the list in order to provide a more detailed recommendation.

https://docs.microsoft.com/en-us/dax/values-function-dax 

  • pfarahani's avatar
    pfarahani
    Helper II
    5 years ago

    Hi Anonymous what I am trying to accomplish is a slicer of departments that only display the departments that are in my fact ables. 

    I imported a (view) that contains all the departments in the database, like 250 departments. So my slicer has this huge list of departments, but in my fact tables I can see that only about 20-25 will ever be used/will ever be in the fact tables.  

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      5 years ago

      Hi,  pfarahani 

      Does the 'Distinct‘ function not work for you?
      If you want to apply a new field to the slicer, you'd better create a calculated table rather than a measure. Then after associating the relationship between the tables, apply this independent field to the slicer.

       

      Best Regards,
      Community Support Team _ Eason