Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |