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.
I am looking for a way to create a measure that returns this numerical value and does not need the creation of an additional table and/or column.
Example: In a 1 column table with the heading "colour" you see.
Red
Red
Blue
Green
Blue
Red
Red has the most occurrences at 3. I want the measure to return 3, not "Red". I've tried TOPN, MAX, MAXX, GROUPBY and some others. It seems like the solutions are to create a new table and/or a new column and then look at that. This seems a bit much to get this value. How is this done without the use of another table or column?
Solved! Go to Solution.
@AaronToth Seems like MODE: Mode - Single Column - Microsoft Power BI Community
In your case:
Mode = VAR myTable = SUMMARIZE(Table,Table[Column],"Count",COUNT(Table[Column]))
VAR myTable2 = FILTER(myTable,[Count]=MAXX(myTable,[Count]))
VAR Mode1 = MAXX(LASTNONBLANK(myTable2,[Column]),[Column])
RETURN
COUNTROWS(FILTER(Table,[Column]=Mode1))
I really appreciate the quick help @Greg_Deckler . It worked flawlessly. I guess the easier way of asking this question was to seek help finding the 'mode'. Thanks again!
With the use of TOPN I am able to get the value in the column that appears most, not the number of times it appeared. It looks like there are some issues with ties when using some of these approaches, but that is okay in my use case. I am not looking for "RED", "BLUE" or "GREEN" where a tie would matter in cases (i.e. blue and green both 2).
Since I am looking at the number of times (most occurrences), then a 2 is a 2. This is why I feel there should be a combination of two/three built in functions to return this value.
@AaronToth Seems like MODE: Mode - Single Column - Microsoft Power BI Community
In your case:
Mode = VAR myTable = SUMMARIZE(Table,Table[Column],"Count",COUNT(Table[Column]))
VAR myTable2 = FILTER(myTable,[Count]=MAXX(myTable,[Count]))
VAR Mode1 = MAXX(LASTNONBLANK(myTable2,[Column]),[Column])
RETURN
COUNTROWS(FILTER(Table,[Column]=Mode1))
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |