Is there a difference between automatic measure and simple manual measure?

[Migasuke]

Hello Experts,

I am facing very interesting case where automatic measure (drag and drop) vs simple SUM creates different outcomes. I thought it's the easiest thing evet but does not seem like and I need some explanation.

Let's briefly explain my case:

1. I have very simple model of 3 tables with DIM in the middle:

2. I created a simple table using (as an example) info from all 3 tables:

3-1. Now when I add measure my table has duplicates.

Measure Revenue = CALCULATE(SUM(Revenue[Revenue]))

3-2. On the other hand, when I use drag n drop of the Revenue column, everything works well. How and Why?

Thank for any idea or help!

[Uzi2019]

hi @Migasuke 

the relationship between country n cost table causing this issue. beacuse there is no filteration from cost to country. means if you want to filter cost country n get the revenue you cant . because there is 1 -> many , single direction from country dim to cost table. 

if you just change the relationship to both with security filter applied. you will get the same result as implicit function is giving.

check the above result.

i hope i answered you question!

[MattAllington]

Your measure is not the same as the implicit measure. You should delete the CALCULATE in your measure to make them the same. Also, I would have thought the relationship, DIM to Revenue, should be 1:many as a modelling principle. 

[Migasuke]

Hello @MattAllington ,

Thank you for you answer. Unfortunatelly, having simple SUM or changing relationship 1:M does not help.
I added copy of my file if that helps.

[Sergii24 · Accepted solution]

Hi @Migasuke, thanks for an interesting case! 🙂

First, to understand the code behind the implicit measure, you can lauch "Performance analyzer" and launch the code in "DAX query view". Within the variable __DS0Core you'll find an interesting filter - "HasDataCosts"

I believe, it's added to the code, to avoid showing potentially "unexisting combinations" in your data model (or better to say combinations that doesn't exist between fact table used in the visual and the table based on which measure is calcualted).

So PowerBI undertands that you are building a measure on Revenue table, which is connected to DIM Countries, therefore, eventhough you ask to display "DIM Countries" and "Costs" in your visual, it will have only values that exist between "DIM Countries" and "Revenue". 

To test and confrim this hypothesis, I've created a dummy table and replaced "Country Code" with the column from a new table "TEST_Table". Similarly to the previous case the implicit measure is filtering our unexisting combinations (see DAX query "Implicit measure - new table"):

Now let's go back to your measure:

Measure Revenue = CALCULATE( SUM( Revenue[Revenue] ) )

If you compare it with "SumRevenue2" in DAX Query view, the code is identical. However, you don't have a filter "HasDataCosts", that we've discussed above. So what really happens?

First PBI plots existing combinations of "DIM Countries" and "Costs" (for example AT: AT,CZ,DE, so 3 rows per each row of  "DIM Countries"). What next? Now you're asking to calculate "Measure Revenue", which has nothing to do with Costs. So what happens for a row "AT" of "DIM Countries" and "CZ" of "Costs"? Well, the only important part to calculate "Measure Revenue" is the value "AT" of "DIM Countries", because this is the one used to retreive rows of "Revenue" table to sum. Therefore for the existing combintaiton AT (DIM Countries) -> CZ (Costs) you get a calcualtion result, which is based on "AT" from "DIM countries" (and is the same all 3 times until "AT" from "DIM Countries" is filtered). 

To obtain the real equivalent of the implicit measure, we need to add that "HasDataCosts" filter, which defines if "costs" actually contains a necessary combination of "DIM Countries" and "Revenue". You can obtain it in the following way:

Measure Revenue (implicit equivalent) = 
VAR _CountriesFromRevenueSelected = VALUES( Revenue[Country Codes (Revenue)] )
VAR _CountriesFromCostsSelected = VALUES( Costs[Country Code (Costs)] )
VAR _Intersection = INTERSECT( _CountriesFromRevenueSelected, _CountriesFromCostsSelected )
RETURN
    IF(
        COUNTROWS( _Intersection ) > 0, 
        CALCULATE( SUM( Revenue[Revenue] ) ),
        BLANK()
    )

Here is the final result:

 I hope it provided some clarity! have a great day 🙂

P.S. check pbix attached for more details 😉

[Migasuke]

Thank you very much.
I still found it odd that the behavior is not the same for implicit measure and "calculated" measure but your solution works fine.

Have a nice day.

[Sergii24]

I agree that it's confusing! It took me quite some time to understand the difference while writing reply on your question 🙂 

I'm glad that my explanation makes sense and you found it useful!