cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WorkHard
Helper V
Helper V

Implicit versus explicit measures

I'm reading and watching tutorials and a lot of people mention that you should avoid using the implicit measures that Power BI conveniently lets you use, like this:

 

biimplicitvsexplicit.PNG

 

No one is mentioning why though. Is this something that people just repeat or is this a convenience thing? I understand that an explicit measure can be reused in other parts of reports but if I start creating a measure for every single count card I have, I'll have a sea of measures.

 

Someone said:

 

"When you design any Power BI report, all measures should be explicit, i.e., you shouldn't allow your end users to create implicit measures because it enables them to do things they shouldn't, e.g., Sum of Unit Price."

 

What's wrong with the sum of unit price?

3 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

edhans
Super User
Super User

A few reasons:

  1. You cannot control what an implicit measure does beyond the basics. Sum, Count, etc.
  2. 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:
    1. Sales = SUM(Table[Sales])
    2. COGS = SUM(Table[COGS])
    3. Margin = [Sales] - [COGS]
    4. Now you are reusing explicit measures in step #3. If you had not created those, you would have to write this:
    5. Margin = SUM(Table[Sales]) - SUM(Table[COGS])
    6. 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.
    7. Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS]))
  3. 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

#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
)


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
edhans
Super User
Super User

A few reasons:

  1. You cannot control what an implicit measure does beyond the basics. Sum, Count, etc.
  2. 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:
    1. Sales = SUM(Table[Sales])
    2. COGS = SUM(Table[COGS])
    3. Margin = [Sales] - [COGS]
    4. Now you are reusing explicit measures in step #3. If you had not created those, you would have to write this:
    5. Margin = SUM(Table[Sales]) - SUM(Table[COGS])
    6. 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.
    7. Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS]))
  3. 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Appreciate the knowledge, do you mind explaining a bit more about the difference in these 3? To me, they look like they will all produce the same result.

Say: Sales = 100 and COGS = 30

 

1. Margin = [Sales] - [COGS] = 70?

2. Margin = SUM(Table[Sales]) - SUM(Table[COGS]) = 70?

3. Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS])) = 70?

 

Where can I read more about this?

#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
)


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you! This makes total sense now. Yes, in that example you gave it will calculate the SUM in "that" context which might be different than the SUM that would be calculated outside that context/filter.

 

Thank you for the lesson!.

@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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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:

edhans_0-1593653322494.png

 

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans @I never said you are advocating it, I was just sharing my thoughts. Thanks for the extra info you shared. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors