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 would like to find the most frequenct value(most repeated times) by DAX. May I know is it possible?
Original Table
Item | ID | user |
1 | 1 | Peter |
1 | 2 | Peter |
1 | 3 | Alex |
1 | 4 | Peter |
1 | 5 | Chris |
1 | 6 | Chris |
2 | 1 | Alex |
2 | 2 | Alex |
2 | 3 | Peter |
Desired Result:
Item | Most Frequency |
1 | Peter |
2 | Alex |
Solved! Go to Solution.
Hi,
To your Table visual, drag the Item field and write these mesures
User count = counta(Data[User])
Most frequency = FIRSTNONBLANK(TOPN(1,VALUES(Data[User]),[User count]),1)
Drag the second measure to your visual.
Hi,
To your Table visual, drag the Item field and write these mesures
User count = counta(Data[User])
Most frequency = FIRSTNONBLANK(TOPN(1,VALUES(Data[User]),[User count]),1)
Drag the second measure to your visual.
Hi,
How can I get the number (how many times repeated the most frequent value).
I'd like to display it in a card.
Thanks
Hi,
Write the formula suggested in my message. If t does not work, then share some data to work with, explain the question and show the expected result.
Hi, apprecaite your feedback.
It works and give me the value "Name of most frequent client", but I need how many time this client name repeated, for example 10 times. I need it as measure not column nor table.
Thanks
Hi,
Drag client to the visual and write this measure
Count = countrows(Data)
Hope this helps.
Hi @Ashish_Mathur , may I have a futher question regarding this formula?
Is it possible I could add a filter in your formula like filter "group" = "B"
Appreciated if you could help
Original:
Item | ID | user | group |
1 | 1 | Peter | A |
1 | 2 | Peter | A |
1 | 3 | Alex | A |
1 | 4 | Peter | B |
1 | 5 | Chris | B |
1 | 6 | Chris | B |
2 | 1 | Alex | A |
2 | 2 | Alex | A |
2 | 3 | Peter | B |
Desired result:
Item | Mast Frequent(B) |
1 | Chris |
2 | Peter |
Hi,
This measure works
Most frequent = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Data[User]),[User count],DESC),1),Data[group]="B")
Hope this helps.
@Ashish_Mathur I am really sorry forget not to mention that "group" itself is a measure.
Its shows the error below when I use the CALAULATE:
A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Is there any way to deal with this case. Appreciated!
Share the link from where i can download your PBI file with your measures already written there.
@Ashish_Mathur Please see the Sample BI
I am hoping I could get the sub-total "supplier" as the most frequently shown supplier in the group"A" rather than a MAX value.
Appreciated!
I am confused. In the Group column, you do not have any entry as A at all. Do not be in a hurry to post. Read your own question multiple times before posting. What exact result do you expect to see in the sub total row and why?
I am hoping I could show the users, for each QTY,
1.)which supplier with the lowest cost & the cost (has been achieved)
2.) Before Expanding the QTY, could it show the most frequenct supplier from all the QTY.
Like your provided measure, except the filter component is a Measure rather a column now.
**Filter "Rank by Supplier" = 1
Supplier 1 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Table[Group]),[User count],DESC),1),[Rank by Supplier]=1)
For this case, the supplier 1's result should be "A" since "A" is the most frequent supplier in all QTY
The reason of doing this is letting the user know generally, which supplier should be used since that supplier with the lowest cost for most cases.
May I know is it achievable?
I still do not understand your expected result. Someone else who does will help you.
You are welcome. If my previous reply helped, please mark that as Answer.
Most Frequent User : =
VAR _userstable =
RELATEDTABLE ( Data )
VAR _groupbyusers =
GROUPBY ( _userstable, Users[user], "@count", SUMX ( CURRENTGROUP (), 1 ) )
VAR _maxcount =
MAXX ( _groupbyusers, [@count] )
VAR _maxcountuserlist =
SUMMARIZE ( FILTER ( _groupbyusers, [@count] = _maxcount ), Users[user] )
RETURN
IF (
HASONEVALUE ( Items[Item] ),
IF ( COUNTROWS ( _maxcountuserlist ) = 1, _maxcountuserlist )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |