Incorrect DIVIDE result when dividing two meausres

[FinancialAdviso]

Hi community!

I am facing an issue with my DAX DIVIDE measure and would appreciate your insights on this .

Data Model Overview:

• Date table: Marked as a date table with unique date fields.
• dim_google:
  • Columns: 'date', 'page location', 'screen_page_views'
  • Data transformation: Originally had timestamp date fields which I converted to Date format and grouped by 'date' & 'page location' to have unique page/date combinations.
• fact_items: Columns include 'date' and 'id'.

Relationships Set:

1. Date[Date] has a 1-many relationship with fact_items[date]
2. Date[Date] has a 1-many relationship with dim_google[date]
3. dim_category[id] has a 1-many relationship with fact_items[category_id], I am using [code] field to filter the fact_items with the filters pane.

Measures Created:

Items_Purchased: CALCULATE( DISTINCTCOUNT(fact_items[id]) )

Add_to_Cart: CALCULATE( SUM( 'GA4'[screen_page_views] ), CONTAINSSTRING( 'GA4'[page_location], "/cart_item" ) || CONTAINSSTRING('GA4'[page_location], "cart") )

Conversion Rate: DIVIDE(
[Items_Purchased],
[Add_to_Cart], BLANK()
)

Problem:

1. When I evaluate the two measures ([Items_Purchased] and [Add_to_Cart]) separately, they provide correct values for whole periods.
2. However, using the above measures, my conversion rate for 2022-04-20 is being calculated as 263.2% when I expect it to be 150/178 = 84.3%. I don't notice any nulls or 0 in numerator and denominator.
3. I also noticed that I'm not able to see the conversion rate for dates before 2022-04-19.

Could someone please help me understand the reason for these discrepancies in the conversion rate calculations? Do I need to create additional relationship between fact_items[date] and dim_google[date].

Many thanks!

[FinancialAdviso · Accepted solution]

After reviewing the data model, the problem was traced back to mismatched date formats from two data sources. The fact_items[date] column had a "ddmmyyyy" format, whereas the dim_google[date] column used the "yyyymmdd" format. Attempting to join data based on these mismatched date formats resulted in errors.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...