Set Analysis - creating a modifier based on count of distinct rows

walkasia — Fri, 03 Mar 2023 16:41:41 GMT

I have a table of household ids and programs, as shown here:

ID	Program
H1	Food
H1	Workforce
H1	Holiday
H2	Food
H2	Holiday
H3	Food

I want two KPIs: one that displays the number of households participating in exactly 2 programs and one that displays the number of households participating in more than 2 programs. The below expresions are not working. I am getting results that I cannot understand, sometimes the same household will show up in both KPIs.

=Count({<[ID] ={"=count(distinct [Program])=2"}>}distinct[ID])

=Count({<[ID] ={"=count(distinct [Program])>2"}>}distinct[ID])

Is this even possible? Where am I going wrong?

TIA

Re: Set Analysis - creating a modifier based on count of distinct rows

Oleg_Troyansky — Fri, 03 Mar 2023 16:46:19 GMT

Hi there!

The expressions appear to be valid. Perhaps the problem is in the combination of the expression and the dimension that you are using it with. If you can post a sample app and highlight your problem there, I'd be able to troubleshoot it for you...

Cheers,

topic Re: Set Analysis - creating a modifier based on count of distinct rows in App Development

Set Analysis - creating a modifier based on count of distinct rows

Re: Set Analysis - creating a modifier based on count of distinct rows