cancel
Showing results for
Did you mean:
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.

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

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

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.

1 ACCEPTED SOLUTION
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)

Choose slicers.

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.

7 REPLIES 7
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)

Choose slicers.

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.

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.

Regular Visitor

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.

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.

Regular Visitor

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.

Super User

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.

Regular Visitor

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

I created a dummy dataset:

created_dateStudentMeal

 8/12/2021 3:47 A Pizza 8/12/2021 10:15 B Burger 8/12/2021 14:45 A Pasta 8/12/2021 23:52 C Rice 8/13/2021 9:04 D Soup 8/13/2021 9:51 E Noodles 8/13/2021 14:26 A Pizza 8/13/2021 17:28 C Burger 8/14/2021 2:56 D Pasta 8/15/2021 4:44 A Rice 8/15/2021 8:08 E Soup 8/15/2021 21:41 B Noodles 8/15/2021 21:59 A Pizza 8/16/2021 5:58 E Burger 8/16/2021 10:13 D Pasta 8/17/2021 0:06 C Rice 8/17/2021 19:19 B Soup 8/17/2021 21:19 A Noodles 8/18/2021 18:11 E Pizza 8/18/2021 20:05 B Burger 8/19/2021 3:50 A Pasta 8/19/2021 9:27 B Rice 8/19/2021 12:40 C Soup 8/20/2021 1:09 D Noodles 8/20/2021 4:45 A Pizza 8/20/2021 12:22 E Burger 8/21/2021 4:25 A Pasta 8/21/2021 8:27 B Rice 8/21/2021 11:36 C Soup 8/22/2021 17:53 D Noodles 8/23/2021 5:20 E Pizza 8/23/2021 9:46 A Burger 8/23/2021 23:17 B Pasta 8/24/2021 4:33 C Rice 8/24/2021 13:26 D Soup 8/24/2021 17:26 E Noodles 8/25/2021 14:10 D Pizza 8/25/2021 23:24 A Burger 8/26/2021 2:08 D Pasta 8/26/2021 12:27 E Rice 8/26/2021 13:12 C Soup 8/26/2021 22:43 A Noodles 8/27/2021 5:38 B Pizza 8/28/2021 15:49 A Burger 8/29/2021 13:48 C Pasta 8/30/2021 10:48 D Rice 8/30/2021 20:13 E Soup 8/30/2021 21:37 A Noodles 8/30/2021 23:12 C Pizza 9/1/2021 13:17 D Burger 9/1/2021 14:38 A Pasta 9/2/2021 2:32 E Rice 9/2/2021 2:38 B Soup 9/2/2021 7:02 A Noodles 9/2/2021 11:20 E Pizza 9/2/2021 15:23 D Burger 9/3/2021 4:02 C Pasta 9/3/2021 14:40 B Rice 9/4/2021 9:57 A Soup 9/4/2021 11:59 E Noodles 9/4/2021 12:48 B Pizza 9/4/2021 20:58 A Burger 9/5/2021 18:10 B Pasta 9/5/2021 22:37 C Rice 9/6/2021 14:34 D Soup 9/7/2021 8:56 A Noodles 9/7/2021 15:04 E Pizza 9/8/2021 14:22 A Burger 9/8/2021 14:27 B Pasta 9/8/2021 21:01 C Rice 9/9/2021 10:05 D Soup 9/10/2021 23:05 E Noodles 9/11/2021 2:42 A Pizza 9/8/2021 9:56 B Burger 9/12/2021 3:12 C Pasta 9/12/2021 18:47 D Rice 9/14/2021 6:22 E Soup 9/14/2021 18:07 D Noodles 9/15/2021 20:29 A Pizza 9/15/2021 21:46 D Burger

Announcements

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.