topic Re: Limit top 10 By measure (Set Expression) in App Development

Limit top 10 By measure (Set Expression)

Aanchal — Wed, 31 Aug 2022 05:17:09 GMT

Dear ,

This is my set Expression

=count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority])

for total no. of colour instances i have to show top 10 .

i used this expression

=Aggr(Rank(count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority])) <=10,[Color Priority])

Its not working . Please Suggest.

Re: Limit top 10 By measure (Set Expression)

sidhiq91 — Wed, 31 Aug 2022 05:50:05 GMT

@Aanchal One small suggestion.

Use a Calculated Dimension as below:

Aggr(if(Rank(count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}

[Color Priority])) <=10,[Color Priority]),[Color Priority])

And Measure would be:

count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority])

If this resolves your issue, please like and accept it as a solution.

Re: Limit top 10 By measure (Set Expression)

Aanchal — Wed, 31 Aug 2022 06:00:47 GMT

Hi ,

Thanks for the quick response .

this is not limiting to 10 .

Re: Limit top 10 By measure (Set Expression)

sidhiq91 — Wed, 31 Aug 2022 06:59:21 GMT

@Aanchal Could you please send me some sample data please?

Re: Limit top 10 By measure (Set Expression)

SunilChauhan — Wed, 31 Aug 2022 08:22:47 GMT

Can we not achive this by Limiting in Dimension

use formula without Rank i.e.

count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority])

and limit dimension for top 10 as in above screen shot.

See the example app also

Re: Limit top 10 By measure (Set Expression)

BrunPierre — Wed, 31 Aug 2022 09:32:10 GMT

@Aanchal I concur with @SunilChauhan on restricting the display values using dimension limits.

Alternatively, maybe this

IF(RANK(Count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority]))<=10,Count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority]))

Re: Limit top 10 By measure (Set Expression)

Aanchal — Wed, 31 Aug 2022 09:49:03 GMT

This is a measure.

Re: Limit top 10 By measure (Set Expression)

Aanchal — Wed, 31 Aug 2022 09:52:39 GMT

its showing only 1 as output

Re: Limit top 10 By measure (Set Expression)

BrunPierre — Wed, 31 Aug 2022 10:30:18 GMT

What is the expected output?

Re: Limit top 10 By measure (Set Expression)

Aanchal — Thu, 01 Sep 2022 04:13:59 GMT

This is desired output , but i have to display only top 10 on basis of no. of instances.

where my no. of instances is a set expression a measure

=count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority])

Re: Limit top 10 By measure (Set Expression)

SunilChauhan — Thu, 01 Sep 2022 04:52:19 GMT

Have you tried this?

IF(Aggr(Rank(count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority])),[SKU Code],[Color Priority]) <=10,count({<[Date Stamp] ={">=$(=Date(Today()-90,'DD-MM-YYYY'))<=$(=Date(today(),'DD-MM-YYYY'))"}>}[Color Priority]))

Re: Limit top 10 By measure (Set Expression)

Aanchal — Thu, 01 Sep 2022 05:37:19 GMT

Yes , this is not limiting to top 10 .

Re: Limit top 10 By measure (Set Expression)

BrunPierre — Thu, 01 Sep 2022 05:50:06 GMT

@Aanchal Try this

IF(RANK( Count(Aggr(Count([Color Priority])),[SKU Code]))<=10,Count([Color Priority]))

Re: Limit top 10 By measure (Set Expression)

Aanchal — Thu, 01 Sep 2022 05:56:11 GMT

Re: Limit top 10 By measure (Set Expression)

BrunPierre — Thu, 01 Sep 2022 06:24:17 GMT

This instead

IF(RANK(Count(aggr(Count([Color Priority]),[SKU Code],[Color Priority])))<=10,Count([Color Priority]))

Re: Limit top 10 By measure (Set Expression)

Aanchal — Thu, 01 Sep 2022 06:28:41 GMT

No, not working.