Fan outs on count distinct calculated measures

Holistics doesn’t currently have the capability to deal with fanouts on measures when used with 1-N model relationships. However, at least if we define metrics with type count distinct, max, min etc the fanout problem is ignored and the query returns OK.

Sometimes, these measures are then used in other measures (e.g. [count distinct x] / [count distinct y]). However the results are not returned, due to a fanout warning, when actually there is no risk of fanout.

It would be helpful to return the value rather than an error.

Hi @Alex_H ,
Could you share more about the issue? A screenshot of your dataset exploration screen might help.

Also, could you check this link < Cannot combine fields due to fan-out issues? | Holistics Docs> to see if this is the underlying issue?


You can see that adding the measure Cart Add → Online Order Rate gives a fanout issue. The measure is simply dividing Online Orders by Cart Adds

Hi @Alex_H ,

This issue happens because your aggregate function is defined inside the sql definition property of a field which, currently, makes the SQL semantic technically infeasible to analyze.
You can change to using our aggregation_type instead so that we can easily detect the aggregation type of a field and prevent fanout issue.

image

Do let me know if you have any questions.

Hi Khai,

I’ve used the aggregation type on the two components of the final measure. However as the components are already aggregates, no aggregation type should be set (I’ve not set the aggregate type but in this case it should be none)

E.g.

Measure 1 aggregation type = count distinct
Measure 2 aggregation type = count distinct
Measure 3 = Measure 1 / Measure 2 (no aggregation type)

Hi @Alex_H ,

Just check with my team and we have not handled composite measure yet, but this is something that we will definitely improve in the future.

As a workaround, you can use field @aml instead of @sql. Please refer to the image below.

image

However, do note that we have not supported aml field in the UI mode so, at the moment, you should not edit the measure a_b in the UI mode.

Do let me know if you have any concerns.

2 Likes

Thanks very much Khai - I’ve applied that and it’s working well. Exciting to see this preview…

1 Like