How to define measures automatically via custom connector

[jatanp]

Hello,

We are planning to build a PowerBI custom connector for our product. The backend would be mostly responding with csv / json tabular data, however this data may require some special interpretation at PowerBI level. Basically we have - and ~ (no rollup) rollup operators which don't work in powerbi aggregation.

We have found the way to define a custom measure using ISINSCOPE function to perform this custom aggregation in matrix and other visualization (it's a standard technique used by many to determine the level of record and multiply the value with -1 / 1 while summing up).

Now the question is that we want to automatically define the necessary measures by our custom connector when the model is loaded in PowerBI. We dont want user to do this every time they load the model.

Is there any way in PowerQuery SDK to achieve this ? I hit upon C# scripts to define the measures but I am still exploring, and am really not clear how C# script would go inside custom connector.

Any help / direction is appreciated.

Thanks !

[v-heq-msft]

Hi @jatanp ,
Thanks for @TomMartens  reply.
Using a template application it is indeed possible to set up the measure in advance during the creation of the report. if you are not able to create a template application you can try to use m code to create custom columns to achieve a similar effect as the measure. Using m code allows you to create from the time you connect to the data source.

Add a custom column - Power Query | Microsoft Learn

Best regards,
Albert He

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

[jatanp]

Thanks @v-heq-msft for the custom column pointer.

I used the same DAX formula for the measure "Level" which I defined earlier to custom column "Level"

and PowerBI did accept the formula without any issues.

Level = VAR __list = { IF(ISINSCOPE(Query1[Level01]),1,-1),
IF(ISINSCOPE(Query1[Level02]),2,-1),
IF(ISINSCOPE(Query1[Level03]),3,-1),
IF(ISINSCOPE(Query1[Level04]),4,-1),
IF(ISINSCOPE(Query1[Level05]),5,-1),
IF(ISINSCOPE(Query1[Level06]),6,-1),
IF(ISINSCOPE(Query1[Level07]),7,-1),
IF(ISINSCOPE(Query1[Level08]),8,-1),
IF(ISINSCOPE(Query1[Level09]),9,-1),
IF(ISINSCOPE(Query1[Level10]),10,-1) }
RETURN 
MAXX( __list, [Value] )

However, it does not work the way the measure worked. It looks like ISINSCOPE function would work only when it is used as part of measure. In case of custom column column, it just keeps returning FALSE.

I tried building hierarchy out of the given levels in powerbi but no help.

Could you please guide and point me into right the direction.

[jatanp]

Thanks @TomMartens , templating would not be possible given the design and the possible usage. Please let me know if any other technique we could implement.

It feels pretty odd that PowerBI provides Model object via Tabular package to program in C# (ref: Automatically create measures in Power BI using VS Code - Phil Seamark on DAX) 

but not in the sdk.

[TomMartens]

Hey @jatanp ,

the Power Query SDK is not the place to define a measure. If you want to create a solution that you want to roll-out to your customers I recomment to create a Power BI Template file:

• Create and use report templates in Power BI Desktop - Power BI | Microsoft Learn

Depending on the way you want to spread your "product", you can also consider to create a template app:

• What are Power BI template apps? - Power BI | Microsoft Learn

Hopefully this provides some ideas of how to tackle your challenge.

Regards,

Tom