Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a question as to why these measures are giving me different results because it does not make sense to me.
I have a Table, we'll call products. There is a column called ActualDate. The Minimum Value I have in this field is 7/1/2016, The MAX value I have in the Actualdate field is 6/30/2017. (currently just 1 year)
Product id count:=CALCULATE(COUNT(Product[ProductID] )) ProductIDCount with date:=CALCULATE(COUNT(Product[Productid] ), DATESBETWEEN(DimDate[Date],[FYSTARTDATE],[FYENDDATE])) FYSTARTDATE:=Min(ProgramYear[Start Date]) FYENDDATE:=Min(ProgramYear[End Date])
FYSTARTDATE = FYSTARTDATE: 7/1/2016
FYENDDATE = FYENDDATE: 6/30/2017
DimDate[Date] is a date table with a large range of Dates, that's all that exists on it and there is a relationship between ActualDate and the Date table.
So my question is what am I missing? I've looked and I have no values outside this date range. Why is the Datesbetween changing the outcome? I'd expect Identical numbers.
Solved! Go to Solution.
Hmm. I cannot seem to replicate this. That being said, the syntax seems weird to me. My suspician is that there is something wonky going on in your relationship between the tables. For example, I was able to replicate this when the things that I was counting did not have matching relationships in the dimDate table. So, I might have 8 items, but I only had 6 dates in my dimDate table. So, do all of your ActualDates match up with all of the dates in your dimDate table?
Would be interested if you could post some sample data in a table that could be used to replicate this issue.
What results are you seeing from these?
Hmm. I cannot seem to replicate this. That being said, the syntax seems weird to me. My suspician is that there is something wonky going on in your relationship between the tables. For example, I was able to replicate this when the things that I was counting did not have matching relationships in the dimDate table. So, I might have 8 items, but I only had 6 dates in my dimDate table. So, do all of your ActualDates match up with all of the dates in your dimDate table?
Would be interested if you could post some sample data in a table that could be used to replicate this issue.
I appreciate your help. How would I get you that large table? it's approx 300,000 rows.
I just verified that no dates are missing from my date table by dumping them all in excel and checking for non matching values.
Just a small sample should suffice. That being said, it could be some sort of problem only at scale. You could also share out your PBIX file on OneDrive or something similar if it is not sensitive data.
I tried to simplify the problem by only grabbing the relevant columns to ensure nothing else is screwing with the results.
So now I have 3 measures:
productCount = Calculate(COUNTROWS(Products)) Result : 347670 Productcount greater than 7/1/2016 = Calculate(countrows(products), Products[actualdate] >= Date(2016,7,1) && Products[actualdate] <= Date(2017,6,30)) Result : 347670 (as expected) Product count with Date = Calculate(countrows(Products),Datesbetween(DimDate[Date],Date(2016,7,1),Date(2017,6,30))) Result 67584
I'll see if I can narrow the dataset for you because this is making my head explode.
The more I got to thinking about it, the more it made sense that SOMETHING was missing from the date table. Then it occured to me it was an issue of Granularity. Because some of the Actualtimes had a timestamp they simply were not matching. I made the select convert the Datetime to Date and my numbers are what they should be. Thank you!
Awesome, glad you got it figured out!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |