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.
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))
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.
Solved! Go to Solution.
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.
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.
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.
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: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 |
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |