7 Replies Latest reply on Aug 11, 2016 7:45 AM by Tom W

# Distinct Count of records based on Dimension

Hello,

i'm trying to determine how to count the distinct number of records in a tableau based on a specific key when the dimension value equals something specific.

Dimension: Opportunity Status Name

Dimension Values: Applied, Accepted, Enrolled, Started

Measure Keys: Inquiry Key, Opportunity Key

The table is structured where the Inquiry Key is unique in the table, but the Opportunity Key has a 1 to many relationship in the table. So essentially, 1 Opportunity Key can have several Inquiry Keys. And I'm trying to get a unique count of Opportunity Key where the Dimension Values equal what I listed above.

Any help would be greatly appreciated.

Thank You

• ###### 1. Re: Distinct Count of records based on Dimension

Hi Faris,

Can you please prepare some sample data and upload a Tableau Packaged Workbook?

• ###### 2. Re: Distinct Count of records based on Dimension

Sure, i've attached a packaged workbook with a very small sample data set.

Thank you

• ###### 3. Re: Distinct Count of records based on Dimension
• Drag 'Opportunity Status Name' to the Rows Shelf
• Right click 'Opportunity Key' and keep the right button held, then drag that to the columns shelf and release
• From the list of options select CNTD.
• ###### 4. Re: Distinct Count of records based on Dimension

Hi Faris,

I think the below should give you what you need:

{COUNTD( IF [Opportunity Status Name] = 'Applied'

OR [Opportunity Status Name] = 'Accepted'

OR [Opportunity Status Name] = 'Enrolled'

OR [Opportunity Status Name] = 'Started'

THEN [Opportunity Key] ELSE NULL END)}

(The { ... } means that the COUNTD is done over your whole table, as defined in your view. For more information about these types of calculations Overview: Level of Detail Expressions gives a good introduction).

Hope this helps!

Jessica

1 of 1 people found this helpful
• ###### 5. Re: Distinct Count of records based on Dimension

Thank you so much guys. The 2nd option that Jessica gave is what I was looking for. Thank you both!