Forum Discussion
Implicit versus explicit measures
- 6 years ago
WorkHard the one reason could be to keep the logic at one place and not to do the same calcuation again and again. for example
if you want to calculate sum of previous date and previous year, you will add two measures like this
Prev Day Sum = CALCULATE ( SUM ( Table[Amount] ), PREVIOUSDAY ( DateTable[Date] ) ) Prev Year Sum = CALCULATE ( SUM ( Table[Amount] ), PREVIOUSYYEAR ( DateTable[Date] ) ) or you can create explicit measure for sum and then use it Base Measure = SUM ( Table[Amount] ) Prev Day Sum = CALCULATE ( [Base Measure], PREVIOUSDAY ( DateTable[Date] ) ) Prev Year Sum = CALCULATE ( [Base Measure], PREVIOUSYYEAR ( DateTable[Date] ) )in this example, if any logic is changed to the base measure it will automatically change dependent measures but in 1st case, you have to change it manually, in most complex models/calculations it becomes really handy and easy to work with. BTW, the above measure example was super simple but just to showcase why it is useful.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
- 6 years ago
A few reasons:
- You cannot control what an implicit measure does beyond the basics. Sum, Count, etc.
- You cannot reuse an implict measure. Say you want to have a Sales measure, a COGS measure, and a Margin measure. If you write explicit measures as follows:
- Sales = SUM(Table[Sales])
- COGS = SUM(Table[COGS])
- Margin = [Sales] - [COGS]
- Now you are reusing explicit measures in step #3. If you had not created those, you would have to write this:
- Margin = SUM(Table[Sales]) - SUM(Table[COGS])
- Believe it or not, #3 and #5 are not the same thing. #3 wraps in implicit calculate around each measure, #5 does not, so to be accurate, #5 has to be #7 to produce intended results.
- Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS]))
- If you go to the trouble to add conditional formatting or other field settings, like a custom number format with an implicit measure then you decide you want to tweak the formula for that measure, you cannot. You have to now create an explicit measure with your correct formula (Say you want to change a budget number to budget + 10% and round to zero places- very simple in a measure - ROUND(SUM(Table[Budget) * 1.1,0). Then you have to remove the implicit measure, and add the explicit measure. Now you have to redo all of the conditional formatting and other field level custom formatting that the visual allows.
There are other reasons, but those hit the highlights.
And one caveat: if you want to use the new Personal Visuals setting, actually implict measures are better as it allows the end consumer to make changes easier. However, I would specifically do this for only those visuals but not for the model itself, and that is still not a reason to use them in general. I would call this an edge case.
- 6 years ago
#1 and #3 are the same thing.
#2 is not.Often they will produce the same results in a simple model, but #1/3 go through context transition because CALCULATE (both implicit and explicit) trigger it. You can read more about it here.
Where this becomes a big deal is in their last example:
DEFINE MEASURE Product[TotalSales] = SUM ( Sales[Quantity] ) VAR SalesOfAllProducts = [TotalSales] EVALUATE FILTER ( ADDCOLUMNS ( VALUES ( Product[Product Code] ), "SalesOfProduct", [TotalSales] <---- This line ), [SalesOfProduct] >= SalesOfAllProducts * 0.01 )It is not the same as this:
DEFINE MEASURE Product[TotalSales] = SUM ( Sales[Quantity] ) VAR SalesOfAllProducts = [TotalSales] EVALUATE FILTER ( ADDCOLUMNS ( VALUES ( Product[Product Code] ), "SalesOfProduct", SUM(Sales[Quantity]) ), [SalesOfProduct] >= SalesOfAllProducts * 0.01 )BUt is the same as this:
DEFINE MEASURE Product[TotalSales] = SUM ( Sales[Quantity] ) VAR SalesOfAllProducts = [TotalSales] EVALUATE FILTER ( ADDCOLUMNS ( VALUES ( Product[Product Code] ), "SalesOfProduct", CALCULATE(SUM(Sales[Quantity])) ), [SalesOfProduct] >= SalesOfAllProducts * 0.01 )
edhans great detail but I do not agree with following
And one caveat: if you want to use the new Personal Visuals setting, actually implict measures are better as it allows the end consumer to make changes easier. However, I would specifically do this for only those visuals but not for the model itself, and that is still not a reason to use them in general. I would call this an edge case.
If there is a business logic need to deploy in the measures, end-user shouldn't work outside that logic and create visuals using their own calculations/measure that leads to some discrepancy, and then we all know what happens. I would let end users only use explicit measures. Just my 2 cents.
parry2k - I am not advocating the use of implict measures, but they do make the Personalized Visual feature more powerful for the end user if they want to tinker around and explore the data. See this article.
The key bit of info:
It is like everything else in Power BI. There isn't often a right/wrong answer, just a better/worse answer, with each side having some pros and cons.