Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i have two sets of data joined by a key, for example
Service Name
A
B
C
Component , Type, Service Name,
1, Legacy, A
2, Legacy, A
3, Strategic, A
4, Legacy, B
5, Legacy, C
In a synthetic measure called "Classification", i'm using the following formula to create this
if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),'Hybrid',if(Count({<Type={'Strategic'}>}distinct Component)>0,'Strategic Only',
if(Count({<Type={'Legacy'}>}distinct Component)>0,'Legacy Only','Neither')))
The idea is that a table will have a single line for each Service Name, and then the Classification field will indicate
Hybrid if there are entries in Type mapped to Service Name, for both Legacy and Strategic, or Strategic Only if there is only Strategic entries, etc. (if theres a better way to do the above feel free to suggest but that isnt the question)
This all works as above for the formula to give me a measure containing, Hybrid, Strategic Only, etc, but i would also like a kpi box to show the total of services where the Strategic and Legacy exist, i.e. where they are hybrid, i thought i might be able to do something like this
if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),Count(distinct Service Name))
But that doesnt appear to calculate the correct value.
Any ideas ?
Also is there a way to make the synthetic measure i've created (Classification) a field users can filter on ?
The only problem with @brunobertels was wrong field names, but I agree that should work. and your filterable calculated dimension wrap your expression around with an aggr and aggregate over fields you want to be selected when you use it as a filter.
Filter:
=aggr(
if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),'Hybrid'
,if(Count({<Type={'Strategic'}>}distinct Component)>0,'Strategic Only'
,if(Count({<Type={'Legacy'}>}distinct Component)>0,'Legacy Only','Neither')))
,[Service Name])
Hybrid - KPI
sum(aggr(
if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0), Count(distinct [Service Name]))
,[Service Name])
)
Hi
May be aggregate your expression by Service Name :
aggr(
if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),Count(distinct Service Name))
,ServiceName)
and you can add your synthetic mesure in a filter pane object to get a list of value , user can select.
Thanks but that just returns " - " as the result ?
The only problem with @brunobertels was wrong field names, but I agree that should work. and your filterable calculated dimension wrap your expression around with an aggr and aggregate over fields you want to be selected when you use it as a filter.
Filter:
=aggr(
if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0),'Hybrid'
,if(Count({<Type={'Strategic'}>}distinct Component)>0,'Strategic Only'
,if(Count({<Type={'Legacy'}>}distinct Component)>0,'Legacy Only','Neither')))
,[Service Name])
Hybrid - KPI
sum(aggr(
if((Count({<Type={'Strategic'}>}distinct Component)>0 and Count({<Type={'Legacy'}>}distinct Component)>0), Count(distinct [Service Name]))
,[Service Name])
)
Perfect thanks