topic Re: TOPN dax function as variable in DAX Commands and Tips

TOPN dax function as variable

raj777karthik — Mon, 12 Sep 2022 15:11:24 GMT

Need to show top 5 services in table visual.

Service Name	Count	Total Count
Apple	43	346
Samsumg	67	346
mi	80	346
oppo	84	346
google	72	346

Measure written we not able to do sum the count of all Services,

Thread_ = VAR A=TOPN(5,SUMMARIZE(VALUES('Stack Tags'),'Stack Tags'[Service],'Stack Tags'[Thread__],"SUM_",DISTINCTCOUNT('Stack Tags'[Thread__])),'Stack Tags'[Thread__],DESC)

Return

CALCULATE(SUMX(A,SUM('Stack Tags'[Thread__])))

Re: TOPN dax function as variable

v-yadongf-msft — Tue, 13 Sep 2022 02:19:24 GMT

Hi raj777karthik ,

This is my test table:

Please try following DAX to create a new table:

New Table = TOPN(5,SUMMARIZE('Stack Tags','Stack Tags'[Service],'Stack Tags'[Thread__]),'Stack Tags'[Thread__],DESC)

Then create a measure to calculate total count:

Total count = CALCULATE(SUMX('New Table',CALCULATE(SUM('New Table'[Thread__]))),ALL('New Table'))

You will get result you want:

If I misunderstand your demands, please feel free to contact us in time.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: TOPN dax function as variable

raj777karthik — Tue, 13 Sep 2022 04:49:11 GMT

Hi,

Thanks for this approach,but we need to do this in existing table considering relationship.If i create table function then only few slicer interacting and not all.

Re: TOPN dax function as variable

v-yadongf-msft — Tue, 13 Sep 2022 06:19:06 GMT

Hi raj777karthik ,

If you don't want to create a new table, pleaser try following DAX to sort [Thread_] column:

Rank = RANKX('Stack Tags','Stack Tags'[Thread__],'Stack Tags'[Thread__],DESC)

Calculate the sum of the top 5:

Total count = CALCULATE(SUM('Stack Tags'[Thread__]),FILTER(ALL('Stack Tags'),'Stack Tags'[Rank]<=5))

Add the [Rank] column to Filters to filter out the top5:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: TOPN dax function as variable

raj777karthik — Tue, 13 Sep 2022 07:00:53 GMT

In this since my service oppo has 84 count the rank is taking all these for 1 and second service mi has 80 count ,rank is taken as 85 .And for third its taking as rank 165.

Re: TOPN dax function as variable

v-yadongf-msft — Tue, 13 Sep 2022 07:45:59 GMT

Hi raj777karthik ,

Please try following DAX to create three measures:

Count = COUNT('Stack Tags'[Service]) Rank = RANKX(ALLSELECTED('Stack Tags'),[Count],,DESC,Dense) Total count = CALCULATE([Count],FILTER(ALL('Stack Tags'),'Stack Tags'[Rank]<=5))

You will get result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: TOPN dax function as variable

raj777karthik — Tue, 13 Sep 2022 08:22:21 GMT

In Rank measure,im getting values 1 and 2.

Re: TOPN dax function as variable

v-yadongf-msft — Tue, 13 Sep 2022 08:25:41 GMT

Hi raj777karthik ,

Can you tell me how the RANK measure did you write?

Best regards,

Yadong Fang

Re: TOPN dax function as variable

raj777karthik — Tue, 13 Sep 2022 08:40:30 GMT

Same as you have mentioned.Created measure for this.

Rank = RANKX(ALLSELECTED('Stack Tags'),[Count],,DESC,Dense)

Re: TOPN dax function as variable

v-yadongf-msft — Tue, 13 Sep 2022 08:56:12 GMT

Hi raj777karthik ,

OK, I know the reason of the problem.

Please try following DAX:

Rank = IF(HASONEVALUE('Stack Tags'[Service]), RANKX(ALL('Stack Tags'[Service]),[Count],,DESC,Dense) )

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: TOPN dax function as variable

raj777karthik — Tue, 13 Sep 2022 09:55:19 GMT

Hi,

Thanks Rank is working fine as expected,but the total count measure is returning all the values.

Total count = CALCULATE([Count],FILTER(ALL('Stack Tags'),'Stack Tags'[Rank]<=5))

Re: TOPN dax function as variable

v-yadongf-msft — Wed, 14 Sep 2022 05:33:51 GMT

Hi raj777karthik ,

Can you tell me how the [Count] measure did you write?

It's better to share with me a screenshot of the original data (hide sensitive information).

Best regards

Yadong Fang

Re: TOPN dax function as variable

raj777karthik — Wed, 14 Sep 2022 05:41:12 GMT

Measure written:

Count = COUNT('Stack Tags'[Service])

Rank = IF(HASONEVALUE('Stack Tags'[Service]),

RANKX(ALL('Stack Tags'[Service]),[Count],,DESC,Dense)

)

Total count = CALCULATE([Count],FILTER(ALL('Stack Tags'),'Stack Tags'[Rank]<=5))

Re: TOPN dax function as variable

v-yadongf-msft — Wed, 14 Sep 2022 06:01:47 GMT

Hi raj777karthik ,

Please replace measure with new column and try again.

Best regards

Yadong Fang

Re: TOPN dax function as variable

raj777karthik — Thu, 15 Sep 2022 05:19:45 GMT

Thanks Yadong.Its working fine.