cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tanmayvs
Regular Visitor

Dynamically count and rank combinations of values in two columns

Greetings!

 

While surfing through this forum, I found an elegant solution for the problem I had, that I wanted to see what are the highest occuring combinations of values in two columns: https://community.powerbi.com/t5/Desktop/Count-and-rank-combinations-of-values-in-two-columns/m-p/21... ; by creating a new table and plotting it. 

 

This however is a static table, and I use time slicers in my data to see how these occurrences change by week/month. I tried several things:

 

1. Tried to create a connection between the two tables (one with my data, and the other that calculates the count and then ranking), however, the only commonality would be time and it doesnt allow me a connection.
2. Tried creating a measure in the original table, but I cant find a way to uniquely assign the scalar count of a combination to the raw data. 


Could you please help me with my situation?

 

EDIT: 

I have created a dummy dataset, of 5 students and their choice of meals at different points of time from Aug-Oct. The overall combination calculation is a separate table that runs the following code:

Table = 
VAR _tb1 = SUMMARIZE(Table1,Table1[Meal],Table1[Student],"Count",COUNTROWS(Table1))
return
ADDCOLUMNS(_tb1,"Rank",RANKX(_tb1,[Count],,DESC))

tanmayvs_2-1637183976621.png

 

This thus shows which was the highest combination of student-meal.

tanmayvs_0-1637183576011.png

I have time slicers in place, but they do not reflect a change in the table. I need to see the top common combinations for the time slicer period too, but I am unable to draw a connection to the calculation table.

tanmayvs_1-1637183729779.png

 

 

 

1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @tanmayvs ,

I am agree with @lbendlin 's point. Please have a try.

Create measures.

 

Count = CALCULATE(COUNT('Table'[meal]),FILTER(ALL('Table'),'Table'[student]=SELECTEDVALUE('Table'[student])&&'Table'[meal]=SELECTEDVALUE('Table'[meal])))
Rank = RANKX(ALL('Table'),[Count],,DESC,Dense)

11.PNG

Choose slicers.

22.PNG33.PNG

Best Regards

Community Support Team _ polly

 

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

 

 

View solution in original post

7 REPLIES 7
v-polly-msft
Community Support
Community Support

Hi @tanmayvs ,

I am agree with @lbendlin 's point. Please have a try.

Create measures.

 

Count = CALCULATE(COUNT('Table'[meal]),FILTER(ALL('Table'),'Table'[student]=SELECTEDVALUE('Table'[student])&&'Table'[meal]=SELECTEDVALUE('Table'[meal])))
Rank = RANKX(ALL('Table'),[Count],,DESC,Dense)

11.PNG

Choose slicers.

22.PNG33.PNG

Best Regards

Community Support Team _ polly

 

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

 

 

View solution in original post

v-polly-msft
Community Support
Community Support

Hi @tanmayvs ,

Please provide some data and desired output.

 

Best Regards

Community Support Team _ polly

 

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

Hello, 

 

I have edited the original post, showing screenshots with dummy data. The desired output is that the calculated table dynamically changes its calculation based on the time slicers. So for example, if I chose August, it should just calculate the data for time.

lbendlin
Super User
Super User

Use of RANKX in Power BI measures - SQLBI

 

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Thank you for the link you sent. I however want it to display the two combinations, namely, the student and the meal for which it is highest occurring for. I couldnt find a way to assign a measure to the existing data, leading me to believe a separate table is to be created; and work dynamically which I'm not able to do yet.

You cannot dynamically create calculated tables. You can use table variables inside measures but the measure result must be a scalar.  What you can do is use measures as page filters to dynamically impact the filters on certain (table) visuals. That can make it look like a dynamic table.

 

Thank you for providing the sample data.  I'll look at it later, and maybe others have input too.

Hello, 

 

I have shown the calculated table that does not dynamically change based on changing time slicer. The screenshots of the visuals are in the edited post. Thanks for your help.

 

 

Table = 
VAR _tb1 = SUMMARIZE(Table1,Table1[Meal],Table1[Student],"Count",COUNTROWS(Table1))
return
ADDCOLUMNS(_tb1,"Rank",RANKX(_tb1,[Count],,DESC))

 

 

I created a dummy dataset: 

 

created_dateStudentMeal

8/12/2021 3:47APizza
8/12/2021 10:15BBurger
8/12/2021 14:45APasta
8/12/2021 23:52CRice
8/13/2021 9:04DSoup
8/13/2021 9:51ENoodles
8/13/2021 14:26APizza
8/13/2021 17:28CBurger
8/14/2021 2:56DPasta
8/15/2021 4:44ARice
8/15/2021 8:08ESoup
8/15/2021 21:41BNoodles
8/15/2021 21:59APizza
8/16/2021 5:58EBurger
8/16/2021 10:13DPasta
8/17/2021 0:06CRice
8/17/2021 19:19BSoup
8/17/2021 21:19ANoodles
8/18/2021 18:11EPizza
8/18/2021 20:05BBurger
8/19/2021 3:50APasta
8/19/2021 9:27BRice
8/19/2021 12:40CSoup
8/20/2021 1:09DNoodles
8/20/2021 4:45APizza
8/20/2021 12:22EBurger
8/21/2021 4:25APasta
8/21/2021 8:27BRice
8/21/2021 11:36CSoup
8/22/2021 17:53DNoodles
8/23/2021 5:20EPizza
8/23/2021 9:46ABurger
8/23/2021 23:17BPasta
8/24/2021 4:33CRice
8/24/2021 13:26DSoup
8/24/2021 17:26ENoodles
8/25/2021 14:10DPizza
8/25/2021 23:24ABurger
8/26/2021 2:08DPasta
8/26/2021 12:27ERice
8/26/2021 13:12CSoup
8/26/2021 22:43ANoodles
8/27/2021 5:38BPizza
8/28/2021 15:49ABurger
8/29/2021 13:48CPasta
8/30/2021 10:48DRice
8/30/2021 20:13ESoup
8/30/2021 21:37ANoodles
8/30/2021 23:12CPizza
9/1/2021 13:17DBurger
9/1/2021 14:38APasta
9/2/2021 2:32ERice
9/2/2021 2:38BSoup
9/2/2021 7:02ANoodles
9/2/2021 11:20EPizza
9/2/2021 15:23DBurger
9/3/2021 4:02CPasta
9/3/2021 14:40BRice
9/4/2021 9:57ASoup
9/4/2021 11:59ENoodles
9/4/2021 12:48BPizza
9/4/2021 20:58ABurger
9/5/2021 18:10BPasta
9/5/2021 22:37CRice
9/6/2021 14:34DSoup
9/7/2021 8:56ANoodles
9/7/2021 15:04EPizza
9/8/2021 14:22ABurger
9/8/2021 14:27BPasta
9/8/2021 21:01CRice
9/9/2021 10:05DSoup
9/10/2021 23:05ENoodles
9/11/2021 2:42APizza
9/8/2021 9:56BBurger
9/12/2021 3:12CPasta
9/12/2021 18:47DRice
9/14/2021 6:22ESoup
9/14/2021 18:07DNoodles
9/15/2021 20:29APizza
9/15/2021 21:46DBurger

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors