User Defined Aggregations - Possible With Semi-Additive Measures?

[Anonymous]

If I have a fact asset bank account type fact table, with semi-additive measure needs (I.E. End User may want to sum current balance amount over customers, but not over time), is there a work around or way to leverage User Defined Aggregations with Semi-Additive measures or are we limited to basic aggs listed below? All documentation I am coming across just shows basic examples of SUM. 

source: https://dax.tips/2021/09/06/intro-to-power-bi-aggregations/

[amitchandak]

@Anonymous , Based on what I got

You can create measure like

AverageX(Values(Table[City]), calculate(Sum(Table[Value])))

AverageX(Summarize(Table, Table[City], Table[Month],"_1", calculate(Sum(Table[Value]))), [_1])

[Anonymous · Accepted solution]

Thank you for idea. I think it sparked a work around for me and to think through and do some more testing on my end. 

I have my Directquery connection to a fact table with ~ 500 Mil rows. then I have an imported summary same fact table at the following grain ( Date | City | Value)

In the data model view, I can elipsis select the Agg table to 'Manage Aggregations', and set my Aggregation Column of [Value] to SUM on the Directquery detail table same column. 

Even though I am striving to use a semi-additive measure, I could just leverage the feature of user defined aggs with the Aggregation at the basic SUM and be purposeful about the semi-additive part by only creating vizualizations with the appropriate grain and columns displayed on X Axis or not. 

It will work for a specific report use case, but I am not crazy about advanced knowledge tech debt and risk of another developer or user misunderstanding or mis-using column. I suppose I could hide it to help mitigate risk. 

Ah trade-off considerations...