Ignore filter in measure, but not in table

[josemaria_cabre]

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

[tresesco]

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)

[josemaria_cabre]

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

[tresesco · Accepted solution]

A tricky one.

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

[josemaria_cabre]

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

Thanks a lot Tresesco!