Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Getting aggregation type of a measure issue

Hello all !

I am trying to get the aggregation methodology for the measure:

 

Screenshot_6.png

By researching the dataView, I have found at the measure's related dataview.table.column (which is a powerbi.data.ISQExpr) the "func" field which has unique numbers for each type of aggregation : 

 

Screenshot_3.png

 

The problem is , the attribute func is not able to reach from code

 

Screenshot_5.png

it is mentioned as not part of the public API:

Screenshot_4.png

 

So , any help to obtain the mentioned field or another way to get the aggregation of the measure is very welcome ! 

 

Thanks in advance ! 

 

1 ACCEPTED SOLUTION

 


  • how could the aggregation be hacked out of the implicit ones? the only thing we saw that changes, as the selected summarization does, is the "column.expr" field, which isn't reachable from the API... 

If you're attempting this, then your assumptions of having to parse the queryName for an implicit measure and extract the aggregation type (probably using a RegEx), or get confirmation from the team around if the func enum is safe to use or not.

I have just tested with a simple explicit measure and this property is not exposed, so would not be a suitable approach for all use cases - using explicit measures is the recommended approach if people do any kind of guided Power BI learning so you cannot guarantee all users will use an implicit measure, unless your use case is very specific and you can control this type of usage within your user base.

 


  • for the explicit ones, isn't the underlying DAX accessible from the "column.queryName" field?  

No, and you can verify this really easily. If you add an explicit measure to a data role, the dataView will just show its expression. For example, I have this measure:

 

$ Sales = SUM(Financials[Sales])

 

The queryName for this measure is as follows:

 

dm-p_0-1605812699577.png

As you'll observe, this is essentially a string pointer to table and measure name (and that's all the implicit measure is doing too). This is all that is required for any data-bound features, such as selection, as Power BI will use this queryName expression to reconcile against the data model, so the DAX is not required and therefore not exposed.

From this point, if you need further assistance on this approach, I have a few suggestions. I appreciate that some developers may have a degree of commercial sensitivity around their work so may not be willing to divulge too much detail, but as custom visual development is so open-ended it is hard to provide targeted assistance without more data to go on.

  1. Consider if your dataViewMapping is correct for your visual's objectives.
  2. If you cannot share publicly, need more targeted support or need to know why the API is the way it is (or what can be done about it), contact the visuals team at pbicvsupport@microsoft.com, or open an issue in their GitHub repo.
  3. If you are willing to share publicly:
    • Provide details of your capabilities.json and an overview of your visual's overall goals.
    • Any specifics around how you intend the dataRoles and dataViewMappings to map to the output as well as any pertinent business logic or user stories that we need to consider.
    • Any code you have so far (ideally a GitHub repo or similar, as pasting multiple raw files in here is going to get really unweildy)
  4. If you need specific assistance beyond this and wish for your work to remain in confidence, some people on the forum can offer consulting services and provide much more dedicated support.

From experience, I think that if you are committed to your approach, then #1 & #2 are where you could focus your attention at present. As previously mentioned, the visuals team do not monitor the forums so you are unlikely to get an official response without contacting them.

Hopefully this provides some possible ways forward for you.

Regards,

Daniel





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




View solution in original post

8 REPLIES 8
dm-p
Super User
Super User

If you want an official answer as to why this isn't in the API, or if it could be added, the team don't monitor the forums - they used to but I don't know why they stopped. Either email them at pbicvsupport@microsoft.com, or open an issue in the GitHub repo for any offical support requests.





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




Also: note that whilst this might be hackable for implicit measures, if someone provides an explicit measure to your visual, you won't know the aggregation type as the underlying DAX is not exposed to custom visuals.





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




Anonymous
Not applicable

thanks a lot for your reply. The distinction between implicit and explicit measures has cleared things up for us. A couple follow-ups:

  • how could the aggregation be hacked out of the implicit ones? the only thing we saw that changes, as the selected summarization does, is the "column.expr" field, which isn't reachable from the API...
  • for the explicit ones, isn't the underlying DAX accessible from the "column.queryName" field?  

 


  • how could the aggregation be hacked out of the implicit ones? the only thing we saw that changes, as the selected summarization does, is the "column.expr" field, which isn't reachable from the API... 

If you're attempting this, then your assumptions of having to parse the queryName for an implicit measure and extract the aggregation type (probably using a RegEx), or get confirmation from the team around if the func enum is safe to use or not.

I have just tested with a simple explicit measure and this property is not exposed, so would not be a suitable approach for all use cases - using explicit measures is the recommended approach if people do any kind of guided Power BI learning so you cannot guarantee all users will use an implicit measure, unless your use case is very specific and you can control this type of usage within your user base.

 


  • for the explicit ones, isn't the underlying DAX accessible from the "column.queryName" field?  

No, and you can verify this really easily. If you add an explicit measure to a data role, the dataView will just show its expression. For example, I have this measure:

 

$ Sales = SUM(Financials[Sales])

 

The queryName for this measure is as follows:

 

dm-p_0-1605812699577.png

As you'll observe, this is essentially a string pointer to table and measure name (and that's all the implicit measure is doing too). This is all that is required for any data-bound features, such as selection, as Power BI will use this queryName expression to reconcile against the data model, so the DAX is not required and therefore not exposed.

From this point, if you need further assistance on this approach, I have a few suggestions. I appreciate that some developers may have a degree of commercial sensitivity around their work so may not be willing to divulge too much detail, but as custom visual development is so open-ended it is hard to provide targeted assistance without more data to go on.

  1. Consider if your dataViewMapping is correct for your visual's objectives.
  2. If you cannot share publicly, need more targeted support or need to know why the API is the way it is (or what can be done about it), contact the visuals team at pbicvsupport@microsoft.com, or open an issue in their GitHub repo.
  3. If you are willing to share publicly:
    • Provide details of your capabilities.json and an overview of your visual's overall goals.
    • Any specifics around how you intend the dataRoles and dataViewMappings to map to the output as well as any pertinent business logic or user stories that we need to consider.
    • Any code you have so far (ideally a GitHub repo or similar, as pasting multiple raw files in here is going to get really unweildy)
  4. If you need specific assistance beyond this and wish for your work to remain in confidence, some people on the forum can offer consulting services and provide much more dedicated support.

From experience, I think that if you are committed to your approach, then #1 & #2 are where you could focus your attention at present. As previously mentioned, the visuals team do not monitor the forums so you are unlikely to get an official response without contacting them.

Hopefully this provides some possible ways forward for you.

Regards,

Daniel





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

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




Anonymous
Not applicable

Hi @dm-p 
I also troubled that things,
because if I use "table"(capabilities.json -> dataviewmappings) I can't get query name... 
https://community.powerbi.com/t5/Custom-Visuals-Development/Table-columns-queryName-property-not-upd...
I want to get query name... how can i get query name?

thanks

mariya

lbendlin
Super User
Super User

You still haven't described what you are doing this for. You don't want to precompute stuff for fun, right?  That's what OLAP cubes and (to a lesser extent) calculation groups are for.

lbendlin
Super User
Super User

What are you planning to do with that information? maybe there is another way of achieving that goal.

Anonymous
Not applicable

Thanks @lbendlin 

What we're trying to achieve is to calculate the aggregated value across dimension members, assuming it is not provided by the API (we asked about this possibility in this post). In order to do so, we need to know the aggregation method of the measure (ex.: sum, min, max, avg, etc.) so that we perform the right calculation. Is this aggregation method provided somewhere in the custom visuals API? we think our best shot at getting it is to parse it out of the field "column.queryName", but we'd like to get official confirmation.
Thanks in advance

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.