Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a custom report where users can select dimensions and measures to bring up a straight table.
That table has data something like the below:
Row Number, No of Seats, Count of PCs
1, 0, 20
1, 10, 20
1, 0, 20
2, 0, 10
3, 10, 10
4, 10, 20
4, 0, 10
5, 10, 20
6, 0, 10
I need to filter out the duplicate row numbers so that only the record with non-zero seats remains. For row numbers that are not duplicated, I don't want to filter anything.
Ex: Row number 1 has 3 records, but I need only the 2nd row which has a seat count. For Row Number 4, I need the 1st row with seat count 10.
So in the above data set, I need the following:
Row Number, No of Seats, Count of PCs
1, 10, 20
2, 0, 10
3, 10, 10
4, 10, 20
5, 10, 20
6, 0, 10
Thanks in advance!
Manas
Hello,
I'm not sure If understood you correctly.
Try to add calculated dimension like that:
=if(aggr(sum([No of Seats]),[Row Number])=[No of Seats],[Row Number],if([No of Seats]=0,null(),[Row Number]))
and suppres null values for this dimension.
Check the example.
May be create straight table using below?
Dimension -- [Row Number]
Expression
1) Sum([No of Seats])
2) Sum({<[No of Seats] = {"=Sum([No of Seats])>0"}>+<[No of Seats] -= {"=Sum([No of Seats])>0"}>} [Count of PCs])