Help with Measure using RANKX and TOPN

cfstout — Thu, 28 Apr 2022 14:52:54 GMT

Hi, I have a table that provides the TOP 5 Property names by using slicers for StartDate and Plan. The table aggregates the count of Sessions and provides a Total. This would be Total Sessions for Top 5 properties based on filter of Plan and filter of Date.

I am needing to create a measure that would provide that same number.

Example: Slicers for Plan2 and 03/01/2022. The has the Top N filter for Top 5 by Session Ct. The Total = 39. The measure would provide that same Total.

I believe this is needing RANKX and TOPN, but cannot figure out how to write it.

Sample Data

Session ID	User ID	Plan	Property Name	StartDate	Station ID
2624657	186585	Plan1	Property 163	3/1/2022	700082-09
2624661	176331	Plan4	Property 241	3/1/2022	100275-05
2624668	118275	Plan1	Property 71	3/1/2022	210186-04
2624813	130501	Plan1	Property 138	3/1/2022	100054-03
2624823	280591	Plan3	Property 490	3/1/2022	200220-02
2624848	198035	Plan1	Property 204	3/1/2022	100200-02
2624862	279846	Plan1	Property 461	3/1/2022	100178-04
2624911	203896	Plan1	Property 20	3/1/2022	200101-02
2624913	178997	Plan1	Property 443	3/1/2022	100600-03
2624928	164836	Plan1	Property 402	3/1/2022	100268-02
2624930	163930	Plan1	Property 163	3/1/2022	700082-09
2624937	251280	Plan1	Property 34	3/1/2022	200050-02
2624940	267771	Plan1	Property 328	3/1/2022	600138-05
2624942	142759	Plan4	Property 325	3/1/2022	200049-03
2624952	119232	Plan4	Property 285	3/1/2022	210077-01
2624956	257189	Plan3	Property 183	3/1/2022	100020-03
2624959	272405	Plan3	Property 387	3/1/2022	100042-04
2624966	163060	Plan1	Property 474	3/1/2022	100189-02
2624971	267825	Plan3	Property 348	3/1/2022	100219-02
2624972	278954	Plan3	Property 183	3/1/2022	100252-04

This is a sample data set. The data set I am working with has over 100k lines, each session id is unique. All other columns will have duplications.

Any suggestions? Thanks in advance for any help provided.

Re: Help with Measure using RANKX and TOPN

v-cazheng-msft — Tue, 03 May 2022 07:37:22 GMT

Hi cfstout,

You may try this solution.

Here are the sample data used.

Create a Measure like this.

TotalSessionsForTop5Properties = VAR Top5_Properties = TOPN ( 5, SUMMARIZE ( 'Table', 'Table'[Property Name], "Num", CALCULATE ( COUNT ( 'Table'[Session ID] ), FILTER ( ALLEXCEPT('Table','Table'[Property Name]), 'Table'[Plan] = SELECTEDVALUE ( 'Table'[Plan] ) && 'Table'[StartDate] = SELECTEDVALUE ( 'Table'[StartDate] ) ) ) ), [Num], DESC ) RETURN SUMX ( Top5_Properties, [Num] )

Then, the result looks like this.

Also, attach the pbix file as reference. Hope it helps.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

topic Help with Measure using RANKX and TOPN in DAX Commands and Tips

Help with Measure using RANKX and TOPN

Re: Help with Measure using RANKX and TOPN