topic Re: Ignore filter in table, but not in measure in App Development

Ignore filter in measure, but not in table

josemaria_cabre — Sat, 16 Nov 2024 18:23:39 GMT

Hi,

Let's imagine this table:

measures are: sum(sales) and count(distinct{<[type]={'sale'}>} name&surname)

If we apply filter 'refund' in dimension 'type', table transforms to this:

Is it possible to achieve this table, keeping count distinct type='sale' calculation (3), but not showing 'sale' type rows in table, using some kind of set analysis?

name	surname	type	Sum(sales)	count(distinct{ < [type]={'sale'} >} name&surname)
Total			-150	3
john	smith	refund	-50	0
mary	bryant	refund	-100	0

LOAD * Inline [ name,surname,type,sales john,smith,refund,-50 mary,bryant,refund,-100 arthur,jones,sale,150 brenda,patrick,sale,75 lisa,jackson,sale,50 ];

Thanks in advance,

Jose

Re: Ignore filter in table, but not in measure

tresB — Thu, 09 Jul 2020 09:02:13 GMT

Do you really want to show the total '3' here or it should be '0'? If you are okay with keeping the total also varrying (to zero in this case), you could try like:

count(distinct{<[type]*={'sale'}>} name&surname)

Re: Ignore filter in table, but not in measure

josemaria_cabre — Thu, 09 Jul 2020 09:10:44 GMT

Hi!

I'd like to show the total '3' (count distinct of type='sale') and sum(sales) = -150, but showing only records with 'refund' type in table

Re: Ignore filter in table, but not in measure

tresB — Thu, 09 Jul 2020 09:25:07 GMT

A tricky one.

=If(Dimensionality()=0,
count(total distinct{<[type]={'sale'}>}name&surname),
count(distinct{<[type]*={'sale'}>}name&surname)
)

Re: Ignore filter in table, but not in measure

josemaria_cabre — Thu, 09 Jul 2020 09:39:14 GMT

Works great. Didn't know about this Dimensionality() function

Thanks a lot Tresesco!