I’m trying to understand why these two measures would return different results. Can someone explain to me how they are different? They are both pulled from the same model using the Sales fact table. Trying to see how many Account IDs had over 1000 in revenue.
Measure 1 =
COUNTROWS(
FILTER(
SUMMARIZE(Sales ,Sales[AccountID],
“Revenue”, [Revenue]),
[Revenue ]>1000))
Measure 2 =
COUNTROWS(
FILTER(
VALUES(Sales[AccountID]),
[Revenue]>1000))
@acnyc88 ,
Using a mockup I did using the Practice Data Set External Tool, these measures should return the same value, which they do as per below.
What results are you getting? The only thing I can think of is that if there is a blank row in account ID, VALUES will return that blank row. But otherwise they should be the same. Note that I did some slight rewrite on the measures per best practices - using ADDCOLUMNS in conjunction with SUMMARIZE to add the revenue column, and actually calling that column directly rather than calling the measure (this distinction is why the “@” prefix is recommended for virtual columns, to distinguish them from measures).
If you can send your PBIX file I’d be glad to take a look at why you’re getting inconsistent results here – I suspect it’s related to your data model.
I hope this is helpful. Full solution file posted below.
– Brian
eDNA Forum - VALUES vs SUMMARIZE Solution.pbix (382.1 KB)
P.S. I noticed above that I used the IDs from the dimension table in the VALUES measure. That’s also best practice (generally filter on dimensions, aggregate on facts), but the results hold if you substitute the Customer Index field from the fact table.
Thanks, Brian. This is very helpful. It’s almost certainly a model issue. I really wanted to validate that these measures should return the same result. In the two measures that you provided, is there a reason I should favor one over the other?
I’ll give you the all-purpose Power BI answer – “it depends”…
I think from a clarity standpoint, the VALUES measure wins, and probably performs better, since the Test Summarize measure uses FILTER and ADDCOLUMNS (both of which are iterating functions, and nested iterators tend to be slower). However, the Test Summarize measure will be more flexible if you are trying to summarize on dimensions from two or more different tables – SUMMARIZE can do that on the extended Sales table, whereas values can only handle columns from the same table. At that point you would need to use CROSSJOIN instead of VALUES, and then worry about filtering blanks…
So, bottom line – simple case, VALUES measure wins; more complex case SUMMARIZE/ ADDCOLUMNS wins.
If you’re interested, here’s why not to use SUMMARIZE to add DAX expression columns:
And why to use the @ convention for naming virtual columns:
I hope this is helpful.
– Brian
This is great info. Thank you!
