Pivot tables in Excel generated by Semantic model and dates

[jmg80525]

Trying out consuming a pivot table by directly embedding a semantic model in Excel.  I have two choices: Instert Table or Insert Pivot. (BTW, it's maddening that the column names in the Table option don't match that in the semantic model.)  I  choose to insert a Pivot Table. (BTW, it does utilize the column names as in the semantic model.) I drop a column in the value in container of the pivot dialog and it only presents Count and Distinct Count. 

I can insert a "normal" pivot table from the table produced from the Insert Table option consuming a semantic model and it will present me with the the full domain of options. 

Why is that?  Any workarounds?  I tried generating a calendar table and conntecting it to the fact table and then placing the date value from the Calendar table, but that didn't make any difference.

[v-priyankata]

Hi @jmg80525 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @jmg80525 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata · Accepted solution]

Hi @jmg80525 

Thanks @lbendlin  for your inputs, whatever suggested is correct.

If you're seeing only limited aggregation options like "Count" or "Distinct Count" when working with a Power BI semantic model in Excel, the issue is likely due to how the fields are defined in the model. To address this, ensure that numeric fields you want to summarize are either explicitly defined as measures in Power BI or are used from fact tables where implicit aggregation is supported.

For best results, open the dataset in Power BI Desktop and create measures for any fields you intend to summarize (e.g., Total Sales = SUM('Sales'[Amount]). Once published to the Power BI Service, these measures will appear in Excel PivotTables with full aggregation capabilities such as Sum, Average, etc.

Also, when connecting to the dataset from Excel, choose “Insert PivotTable from Power BI dataset” instead of “Insert Table.” This approach ensures Excel fully recognizes the semantic model structure and provides the complete set of summarization options.

If you're using a date field, make sure it comes from a properly configured Date table in Power BI with a valid relationship to your fact table.

Thank you.

[jmg80525]

Didn't answer the question, but good to know. I am consuming the semantic model directly in Excel.

[lbendlin]

If you want to use a semantic model in "live" mode (aka "Analyze in Excel")  then you must predefine all measures explicitly in the semantic model. Implicit measures will not be available.