Distinct Count Issue

[alicia_pbi]

I have 2 tables that have lists of contract numbers from the exact same source, manipulated in different ways but leaving the contract number in each table.  When I try to create a relationship, it works but for some reason about 400 of the 1400 contracts are not being represented.  I have trimmed my contract numbers and confirmed they are both in TEXT form.  When I go to the Query Editor I am able to filter by my test contrat number in the query that is NOT counting that contract number in my canvas.  I am also able to filter by this contract number in the dataset view of the PBI desktop.  

It must be something very silly but can anyone provide suggestions for why most of my contracts are being counted but some are being excluded from a query?  To clarify I have a visual representation also showing the table on the left representing both queries A & B.  The table on the right represents query B in which I can show the contract number but the table on the left is now counting this in the column "Title".

[Anonymous]

Hi,

Could i ask about the source of your data?

Might be a long shot, but it sounds like it could possibly be on a query where you are getting limited results, maybe to 1000? Possibly a Direct Query?

Many Thanks

POC

[Greg_Deckler]

Can you provide a sample of your contract numbers? Perhaps some issues with capitalization? It is tough to tell from your graphics what is going on. So are you saying that you have two tables with contract numbers, that's about all I understand I don't understand the underrepresented.

[alicia_pbi]

Hello @Greg_Deckler 

Due to my companies data policy I cannot provide a live dataset I can only provide the screenshot.

The values have been validated as existing in both tables in both the dataset view of PBI and the Query Editor view.  I was able to successfully filter for one of the 'missing' document numbers in both of these which is why I am so confused.  That is why I trimmed the contract numbers to remove any unnecessary spacing and validated that they are all in fact TEXT as opposed to some being TEXT and some being number.  

I guess my question is if the sample contract number can be found in the Query Editor and the dataset view, the number is EXACTLY the same, and the format is the same, why is it not being counted.  As shown in my picture contract ending in 745 is in both table A & B.  However I am only able to count the contract ending 745 in table A; table B represents a blank.

[Greg_Deckler]

No worries but I have the philosphy that most complex issues can be simplified and broken down into a sample model that generally includes less than 10 records. I also have the philosphy that those who aren't willing to put forth the effort to assist those trying to help for free are probably not deserving of said free assistance.

[alicia_pbi]

Hello @Greg_Deckler 

I was under the impression this was a site for assistance and if you do not have anything constructive I am not sure you need to provide a response.  While I appreciate your suggestions I find your latest response unnecessary.  In the future I urge you to simply not respond if you disagree.  I have used this site many times and received a lot of assistance but now am feeling reluctant about using going forward given this experience.

[Anonymous]

Hi Alicia,

Pay no attention to those that cannot control themselves. This is a positive place to get assistance and like yourself, i am here all the time.

Unfortunately, some do have an attitude of giving you grief like they have here if you do not comply with what they have asked of you to help resolve it. You are going to need to just ignore them as they are not going away and is the natural state of the internet.

As i mentioned, have a look at your data source from a perspective of how the data is being obtained to make sure you are not getting limited queries.

Many Thanks

POC

[alicia_pbi]

Hello @Anonymous 

The data source is Analysis Services importing the data.  The data source is a cube I don't have access to view so am unable to use an MDX query so I have to import the data.  I have confirmed that the data set doesn't have any missing details.  I pulled the table from the Dataset view into Excel and am able to filter to find a specific contract but it won't be counted.

I have been doing additional analysis and it *seems* the issue appears with the count, first, last, or distinct count function selected.  When I choose "Don't Summarize" the contract number appears in my visual table but disappears when I choose a different aggregation.  This leads me to believe the issue is somehow with how the data is represented in the query however I have confirmed in both tables the data should be in text which to my understanding should allow for a count.  Have you ever experiences an aggregation where it appears with "Don't Summarize" but cannot be aggregated in a count?

Thanks so much for your time!

[Anonymous]

Hi @alicia_pbi,

I only get count issues like you are describing when the count gets to 0 value and i havent used a column to present the 0 value.

I am a little stumped with your issue.

Are the contract numbers all numbers? if so, why are you converting to Text? Maybe unique values on a numeric column will yeild a better result.

Many Thanks
POC

[alicia_pbi]

Hello @Anonymous 

Yes the contract numbers are all numerical values but I chose text for 2 reasons 1. the contract numbers may have preceeding zeros and 2. I typically always translate numberical values I do not plan to sum to text values to avoid incorrect aggregation in the models.

When I merged the two columns of contract numbers, I have many blanks (indicating that the contract number does not exist in the 2nd table) but when I export the visual from PBI into Excel I am able to easily see that each contract number exists in both tables which resolved my issue of confirming that the contracts existed in both sources.  My PBI issue of aggregation is still not resolved however I had to move on in my model to meet deadlines.  I will circle back to this thread when I have had additional time to research why the aggregation doesn't count my unique contract numbers even though they all exist in both sources.  Thanks for your time and assistance 🙂

[v-kelly-msft]

Hi @alicia_pbi ，

Please try the following tests:

• Create a table visualization only with the contract number from both tables
  • Check if there is any that is blank
  • This will give you the certainty that all contracts are in both tables
• Make a matrix visualization with the following setup
  • Rows: Contract number (from the table that has the one side from the relationship)
  • Columns: Contrat Active
  • Values: contract active count
    • check the total values on the columns

Show me a feedback once you have done the tests.😊

In my opinion,there should be some errors of your data in your column that make the aggregations you need can't be done.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[MFelix]

Hi @alicia_pbi ,

I have to agree with @Greg_Deckler , believe that all of the options that can have impact on this calculation have been asked (relationship type, information matching on both table, calculations made, type of visualizations used) withouth additional information is difficult to help you.

It's not the first post where I ask someone if the values are matching in both tables or if they have divergent data and they say no and when they share a sample file the truth is that there is data errors that people didn't saw or weren't aware.

I understand that the information is sensitive but if you read @Greg_Deckler  post about How to Get Your Question Answered Quickly you will get a good guide to have a good way to help others to help you.

Hope you can solve your issue and I'm available for further assistance if you need  (believe I can talk by @Greg_Deckler  also because he loves to help others and that's why he is a first generation Super User).

[Anonymous]

Hi @MFelix MFelix,

Regardless of the validity of the statement from @Greg_Deckler, the toxic blunt approach is neither professional or helpful. I get his point and would have done the test file if this was me, but there is just no reason to behave the way he has. @alicia_pbi is just looking for some help ina  restricted and complex environment, not exactly the crime of the century!

Many Thanks
POC

[MFelix]

Hi @alicia_pbi  and @Anonymous ,

Not making any excuses or justifications (for me or @Greg_Deckler ) the intent of our questions and remarks is not to be toxic or blunt, but has you refer having complex models and generic questions the difficulty to help others increases.

None of the users in this forum is maknig this to earn any money or to take advantage from other users simply to help, but this is a two way street and the ones that ask for help also need to be willing to take the effort to explain in the best way possible otherwise we enter in suppositons or incomplete answers that is even worst.

Again I rephrase my last sentence on the previous post if you need help I'm available howwever there is questions that need to be answered otherwise we cannot replicate the error.

@alicia_pbi 

Looking at what you have written this should return the correct result can you please try the following tests:

• Create a table visualization only with the contract number from both tables
  • Check if there is any that is blank
  • This will give you the certainty that all contracts are in both tables
• Add to this table the Contract active yes or no
  • Check if all values in the column is filled out
• Make a matrix visualization with the following setup
  • Rows: Contract number (from the table that has the one side from the relationship)
  • Columns: Contrat Active
  • Values: contract active count
    • check the total values on the columns

If you can make this tests we can maybe get some additional information that your images are not giving us.

[Greg_Deckler]

What is doing the counting? The default count aggregation? A measure? How is the relationship defined? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[MFelix]

Hi @alicia_pbi ,

When you create a relationship between tables you need to take into attention that if in both tables the ID's are not the same you can get blank values. I believe this is what is happening with you.

Let's assume these two tables

ID	Contrat
1	A
2	B
3	C

ID	Contrat
2	B
3	C

If you make a connection between this two table contract 1 will appear as blank depending on the column you use on the visualizations since it's only refering to one of the tables.

Usually when having this type of tables that not all records are on both of them you need to create a dimension table that has all the ID's and then make the relationship between the two tables and the dimension table.

Another thing to take into account is that you need to use the dimension table as your context on the visualizations so you can have the correct resutls.

My questions is do both tables have all values, or are there any values only showing in one of the tables (A or B)?

[alicia_pbi]

Hello,

The values have been validated as existing in both tables in both the dataset view of PBI and the Query Editor view.  I was able to successfully filter for one of the 'missing' document numbers in both of these.  Which is why I am so confused.  That is why I trimmed the contract numbers to remove any unnecessary spacing and validated that they are all in fact TEXT as opposed to some being TEXT and some being number.  

I guess my question is if the sample contract number can be found in the Query Editor and the dataset view, why is it not being counted.

[MFelix]

Hi @alicia_pbi ,

How do you have you relationship setup?

Can you share a mockup file?

[alicia_pbi]

Hello @MFelix 

I cannot share the file nor do I have time to create a sample file as my full file has 15+ data queries pulling from Analysis Services & SQL with over 20 relationships and millions of rows of data so that would just take too much time to create.

The relationship is a many to one relationship based on the contract number.  Table A has the contract number (many) listed while Table B has the contract number (single/unique value) listed.  From Table A I have another column indicating whether the contract is active and I need to be able to slice Table B based on the active contract value.  The reason I am not using the MERGE option is because I would need to create yet another table to use as a related table for the Active Contract slicer and was hoping to avoid an additional step.

[v-kelly-msft]

Hi  @alicia_pbi ,

What is the orientation in the relationship between table A and table B?If it isnt both,change it to both.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[alicia_pbi]

Hello @v-kelly-msft 

I am unable to make the direction "both" because it will create ambiguity among several other tables within the model.

Thanks for the suggestion!