• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

DISTINCTCOUNT - Pivot table context not respected|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / DISTINCTCOUNT - Pivot table context not respected|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotDISTINCTCOUNT - Pivot table context…
sp_PrintTopic sp_TopicIcon
DISTINCTCOUNT - Pivot table context not respected
Avatar
Claudine Beunens
Member
Members

Power Query
Level 0
Forum Posts: 33
Member Since:
April 20, 2018
sp_UserOfflineSmall Offline
1
January 21, 2022 - 6:51 am
sp_Permalink sp_Print sp_EditHistory

Hi,

We have account Groups with a explanatory text field Purpose, applied across many entities

Trying to write a measure to see all account Groups where we don't have unique Text in that field purpose, and would like to see the distinct count if it's not unique

First part to define the distinct count doesn't seem to work, the outcome is not respecting the field Group of my pivot

=DISTINCTCOUNT(Group_Settings[PURPOSE])

File is attached

Thanks!

Claudine

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
January 21, 2022 - 2:20 pm
sp_Permalink sp_Print

Hi Claudine,

You have two tables in your model with the same names: one called Group Settings and another called Group_Settings. Your PivotTable is built on Group Settings, but your DISTINCTCOUNT measure is referencing Group_Setttings.

If you change it to:

=DISTINCTCOUNT(Group Settings[PURPOSE])

It appears to work.

Mynda

Avatar
Claudine Beunens
Member
Members

Power Query
Level 0
Forum Posts: 33
Member Since:
April 20, 2018
sp_UserOfflineSmall Offline
3
January 21, 2022 - 6:32 pm
sp_Permalink sp_Print

Hi Mynda

thanks !! you gave me the hint I needed, this is what happended

  • query Group Settings was initially imported into a table (but not added to data model) - Table name Group_Settings
  • changed the setting of my import and ticked "add to data model" , so was creating that second query Table Group Settings

I changed as following

  1. update of query : made it connection only to remove the table & data in data model
  2. re-generated the load with the option to add to the data model

I have now 2 sets of data in the Pivot table fields

1. Group_Settings : Data Source Query

1. Group_Settings 1 : Data Source Table Group_Settings

Distinctcount works now (attached)
I assume all measures will have to be created from the query table, and all pivots build from there as well right?

Or will I still need the data source table as well?

Thanks !

Kr Claudine

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
January 22, 2022 - 8:55 am
sp_Permalink sp_Print

Hi Claudine,

No file attached. Be sure to click 'start upload' and wait for the grey check mark beside the file size before clicking submit.

Mynda

Avatar
Claudine Beunens
Member
Members

Power Query
Level 0
Forum Posts: 33
Member Since:
April 20, 2018
sp_UserOfflineSmall Offline
5
January 23, 2022 - 11:18 pm
sp_Permalink sp_Print

Hi Mynda,

I am really struggling with DAX. Just decided that I really need to get a grip on the basics, to have a full understanding on the key dimensions before starting to look for posts and adjusting my measures with a trial and error, that doesn't work.
So that's what I am going to do in coming weeks 🙂

Maybe you can help me out with this one, as it would be great to have this particular one included in the dashboard I am building, it's still with the same data of this original post.

There is a list of Groups with a explanatory text field Purpose, applied across many Companies, Grouped by SSC
I am trying to write a KPI measure (on SSC level) to obtain the unique count of Groups (within an SSC) where we don't have unique Text in that field "Purpose" and this should only be done for rows that have a value in the column DIM_GROUPS.Group Description

I made a measure ">1 Purpose value", but got stuck

1. it only shows correct data when the Group is included in the pivot/chart - it should work for both , also when we just summarize on SSC level (dashboard summary)
2. I still have to add the formula where I count the Groups that don't have unique purpose text, value 1 is OK, means they are standard, so only the ones >1 should be counted

If this is peace for cake for you then it would be great if you could help this baby Dax user 🙂

I attached the file

Thanks!

Kr Claudine

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
January 24, 2022 - 4:05 pm
sp_Permalink sp_Print

Hi Claudine,

I'm not sure I understood the requirement, but please see if the measures Puprose >1 and Purpose Blank in the attached file are correct.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Claudine Beunens
Member
Members

Power Query
Level 0
Forum Posts: 33
Member Since:
April 20, 2018
sp_UserOfflineSmall Offline
7
February 1, 2022 - 12:44 am
sp_Permalink sp_Print

This solution addressed the requirement , thanks a lot Mynda!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ayal Telem, Mark Stevens
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27292

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.