Hi All,
I recently normalized a flat table to dimension tables. My customer column from the dimCustomer table count and the fctSales[Customer] table give me the correct count in Power Query using the Column Profile.
But when I am in report view in Power BI , I use a measure to count the number of Customers from the dimCustomer table it gives me 1279 and fctCustomer[Customer] gives me 1311. There is a shortage of 32 customers from the dimCustomer table. I cannot seem to figure out what the problem is. The count of numbers should be same from dimCustomer and fctCustomer[Customer] table.
Any help or suggestion would help to debug thisā¦
Your dim table should only have one occurencs for each customer id. Your fac tables will have several occurences for that customer id which will be correct.
Your statement the count in dimcustomer and fact customer table is incorrect.
How did you do your count measurement?
Have you tried DataMentor/EDNA AI tools that is built within the EDNA learning platform? Give it a try to help you with a solution.
Thanks for feedback, please see my screenshot.
When using Power Query, I checked the customer count from Customer table, it shows 1316, and even the Sales table - Customer column gives the correct number of 1316.
But when I use the DAX measures in Power BI report view, the Customer count shows 1279 as per the screen shot. Iām not sure how I can tackle this part.
Hi Keith,
Sorry for the trouble. I have used the Customer number to create a relationship from dimCustomer to Sales[Customer] column. I have the screen shot below:
As you can see there is a blank row. When crosschecking in the excel file, I noticed that there are some new customers that were added recently, they show up in the Sales fact table but donāt show up in the dimCustomer table. Iām not sure why this is happening. I refresh the model every day in the morning, so the sales are updated but the dimCustomer table is not getting updated i guess.
I would say that your dimcustomer table isnāt refreshing its with your fact table by referencing to fact table.
Did you duplicate from facttable to create your dim customer? If you duplicate it wonāt pick up all your changes that your have from facttable.
You need to reference not duplicate
Look up references process within Datametor or follow the course that @mspanic has mentioned.
do a drill down on the blank to see what comes up that makes up your amount.
Hi @chris786 - As Keith mentioned, How are you creating your Dimension table from Fact table. Is it referencing from Fact table or are you duplicating all the steps from Fact table and then doing āRemove Duplicatesā etc. If anything else, do share.
Also, check if āEnable Refreshā is ON for your dimension table.
I think this might be the solution you both are proposing, as I remember i had duplicated the fact table and then removed duplicatesā¦Iām sure this is the culpritā¦
I did not get time to check this, I will try it out by referencing and then removing duplicates and will let you both know the outcomeā¦
Thanks for your patienceā¦
Hi Ankith / Keith,
I tried using the Reference method, Iām getting issues, once I reference the fact table, I then remove all unnecessary columns and keep only the customer details, then I remove duplicates. I then go into Sales table and remove all the customer related columns except the Customer Code column. When I perform a refresh, the customer dim table says " Customer Name" not found, obviously because I have deleted those columns from the Sales table. But the point is to remove the unnecessary columns from Sales table, so how do i go about it.
Hi @chris786 - Based on explanation above reference will not work. Create two queries, one for Fact and other for Dimension. Keep Source say excel as same and perform individual steps as needed in both.
If you are doing this and still getting issue, then either share the data or the PBIX file else we will keep going to and Fro without knowing whatās the issue.
Hi Ankith,
I used Duplicate method and its working. This time both
āEnable loadā and āInclude in Report Refreshā and checked.
Previously I might have unchecked āReport Refreshā. I hope this is correct what Iām doing.
To check the data, I added a couple of dummy customers in the source excel sheet and clicked refresh, and it works.
Hi @chris786 - Great, issue is resolved. I think i mentioned in earlier reply also to check āEnable Refreshā is on. Yes, what you are doing is correct.
As the issue is resolved, please mark this post as Solved.
