Issues with day wise count of orders after joining two tables using date dimension as a separate tab

[tanveermukhtar]

Hi,
I have two different tables.
Table 1 = 'Brands Data'. 
Columns(Brands Data) = Date, Order_id, Category, subcategory

Table 2 = ' Brand shares'
Columns(Brand shares) = Date, subcategory, total shares

Table 3 = 'DateDim'
Columns(DateDim) = Date, week, month quarter

I have joined the first two tables with DateDim table  using Many-1 relationship for date column and category with category, and subcategory with subcategory using many-to-many relationship for the other two tables.

Now I want to have a visual matrix where I have date-wise count of orders from 'Brands Data' table and sum of 'total shares' from 'Brand Shares' table when the 'Category' in 'Brands Data' table is "Brands".

I use the date column from 'DateDim' table, count of order_ids from 'Brands Data' table and sum of total shares from 'Brand shares table. The issue is the count of order_ids day-wise is not accurate. It's almost the same count everyday, which is almost equal to the total count of all the dates combined

Have tried all sorts of DAX functions, created separate measures, tried date columns from the respective tables but nothing seems to work

Any help would be super wonderful

[tanveermukhtar]

Here's some feedback on what I tried:

The count of distinct dates in 'Brands Data' table is correct. It matches with the DateDim table. Both have 109 count which is the number of days for which the data is available.

It won't match with the count of disctinct dates for 'Brand shares' table because data in that table for a limited duration(recent weeks only). But that's still ok because I need the ratio of orders to shares for the last few weeks only.
Now the 'Date' in 'Brands Data' table has column name 'Order_create_date'. I created the measure 'Order Count' and plotted the Date from Brands Data table and from DateDim table to see how the day-wise count shows.

As you can see, the dates repeat for order count. Also, the dates should be the same from both 'Brands Data' table and from 'DateDim' table, but they are completely different.

[123abc]

It seems like there might be an issue with the relationship between the 'Brands Data' table and the 'DateDim' table. The fact that the dates are not matching between the two tables indicates a problem with how the relationship is set up.

Here are a few steps to troubleshoot and fix the issue:

1. Check Relationship Type: Ensure that the relationship between the 'Brands Data' table and the 'DateDim' table is set as a Many-to-One relationship, with the 'DateDim' table on the "One" side.

2. Check Date Formats: Verify that the date formats in the 'Order_create_date' column of the 'Brands Data' table and the 'Date' column in the 'DateDim' table are consistent. Sometimes, date-related issues can occur due to different date formats.

3. Use RELATED Function: Instead of directly using the 'Order_create_date' column, try using the RELATED function to refer to the related date in the 'DateDim' table. Update your 'Order Count' measure as follows:

Order Count = COUNTROWS(FILTER('Brands Data', 'Brands Data'[Category] = "Brands" && NOT(ISBLANK(RELATED('DateDim'[Date]))))

1. This ensures that you are using the related date from the 'DateDim' table.

2. Review Data Quality: Check for any data quality issues in the 'Order_create_date' column of the 'Brands Data' table. Look for duplicates, missing values, or any anomalies that might be causing the mismatch.

3. Recreate Relationships: If the issue persists, consider deleting and recreating the relationship between the 'Brands Data' and 'DateDim' tables. Make sure that the relationship is based on the correct columns.

After making these adjustments, refresh your data and check if the date-wise count is accurate in your visualizations. If the problem still persists, further investigation into the data and relationships might be needed. Additionally, tools like DAX Studio can help you analyze and debug your DAX measures more effectively.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[tanveermukhtar]

Some updates:

The countrows formula with related gives me the following error:

The relationship between 'Brands Data' and 'DateDim' for dates is many-to-1

Date format for both is the same unless the format needs to be changed somewhere else

Deleted the relationship and re-established. Refreshed the data. 

The data in 'Brands Data' table is ok with no missing or duplicate order_ids

[tanveermukhtar]

Thanks for the detailed help.

Let me try these and get back with feedback

[123abc]

It seems like you're facing an issue with the accuracy of day-wise order counts in your visual matrix after joining the tables. Let's try to troubleshoot and improve the DAX measures.

Assuming your relationship between 'DateDim' and 'Brands Data' is based on the 'Date' column, and 'Category' is a common field for the many-to-many relationship, let's create the measures for your visual matrix.

Order Count = COUNTROWS('Brands Data')

Sum of Total Shares for Brands:

Total Shares for Brands =
CALCULATE(
SUM('Brand shares'[total shares]),
'Brands Data'[Category] = "Brands"
)

Now, let's create the matrix visual with the 'Date' column from the 'DateDim' table, 'Order Count' measure, and 'Total Shares for Brands' measure.

If the issue persists, we might need to consider the relationship between 'Brand shares' and 'Brands Data'. Ensure that the relationship is correctly established. Also, double-check if there are any filters or slicers affecting the results.

Additionally, you can create a few diagnostic measures to better understand what might be causing the issue:

1. Distinct Dates in Brands Data:

Distinct Dates in Brands Data = COUNTROWS(VALUES('Brands Data'[Date]))

1. This measure will give you the count of distinct dates in the 'Brands Data' table. If this count is unexpectedly low, there might be an issue with the relationship or data.

2. Distinct Dates in Brand Shares:

Distinct Dates in Brand Shares = COUNTROWS(VALUES('Brand shares'[Date]))

1. Similar to the previous measure, this one gives you the count of distinct dates in the 'Brand shares' table.

2. Distinct Categories in Brands Data:

Distinct Categories in Brands Data = COUNTROWS(VALUES('Brands Data'[Category]))

1. Ensure that the count of distinct categories is as expected.

By examining these diagnostic measures, you can identify if there are any issues with the data, relationships, or measures that might be causing the inaccurate day-wise order counts.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.