Forum Discussion

davidfelipepg's avatar
davidfelipepg
Frequent Visitor
2 years ago
Solved

Why can I create implicit measures in some Excel pivot tables connected to Power BI data models?

Since I used Power BI, my Excel pivot tables connected to the data model only allowed me to use the explicit measures defined in the model.

But I have recently discovered that some of my data models, when I analyze in Excel, allow me to create implicit measures from any field from my model (that's great!!!). But I don't know why...  😕

Can someone explain to me how to convert the models, in which I cannot create implicit measures in Analyze in Excel, to models in which I can use implicit measures? (I've been searching the documentation for a while and investigating the differences between data models and I can't find an explanation.)

 

Thanks a lot!!

 

Note:

I've seen that in the models in which I can create implicit measures the tables look like this, in Excel:

 

While in those that cannot be done, like this:

(Both examples come from Power BI data models)

Need Help
  • GilbertQ's avatar
    GilbertQ
    2 years ago

    Hi davidfelipepg 


    The reason for this is that in the advanced settings using Tabular Editor you can enable or disable Explicit Measures. There are some requirements such as Calculation Groups which cannot have implicit measures.

4 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    2 years ago

    Hi davidfelipepg 


    The reason for this is that in the advanced settings using Tabular Editor you can enable or disable Explicit Measures. There are some requirements such as Calculation Groups which cannot have implicit measures.

    • davidfelipepg's avatar
      davidfelipepg
      Frequent Visitor
      2 years ago

      Thank you very much for the response, since you have given me the key. In my case it is the Calculation Groups (that I use a lot) that disable me the implicit measures.

       

      Thanks to you, I've discovered the Model property that enables or disables implicit measures. In case it can help someone, in Tabular Editor, in the Model properties, there is the option Discourage Implicit Measures, that can be modified: 

      However you cannot change it when you have Calculation Groups in your model:

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, davidfelipepg 

     

    Implicit measures in Excel are more likely to be available when the data model in Power BI is structured in such a way that Excel can interpret a single field as a latent measure. This includes columns of numbers that have data that Excel can aggregate, such as sums, averages, and so on. For more insights into how the Power BI data model interacts with Excel, you may find this document helpful:

     

    Troubleshoot the connection from Excel to Power BI data - Power BI | Microsoft Learn

     

    Create a Measure in Power Pivot - Microsoft Support

     

    Excel's ability to create implicit measures may also depend on the semantic model used in Power BI. Excel workbooks can act as semantic models in Power BI, and the way you import or connect to those models can affect the functionality available in Excel. You can check the follow link:

     

    Basic concepts for designers in the Power BI service - Power BI | Microsoft Learn

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • davidfelipepg's avatar
      davidfelipepg
      Frequent Visitor
      2 years ago

      Thanks a lot for your response. 😊 See the previous post for the solution