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

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

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

This solution addressed the requirement , thanks a lot Mynda!