Forum Discussion
User Defined Aggregations - Possible With Semi-Additive Measures?
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/
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...
2 Replies
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])
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...
