Forum Discussion
DAX - Most Frequent value
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.
Ashish_Mathur Thanks Ashish, it works fine in my model. Appreciated.
You are welcome. If my previous reply helped, please mark that as Answer.
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!
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
Excellent solution! But can it easily be turned into Custom Columns instead of Measures? The reason I ask is that I have implemented your solution for two different fields and would like to be able to collapse and expand for each of them.
Thank you. I cannot understand why you want to convert a measure into a calculated column formula.
I have broadcasting data where each program and each episode has a unique ID. There are however more than one program- and episode name behind each ID. Ideally, I would like to have a Matrix visual where I could expand or collapse a selected program or episode - using the most frequent name as a proxy and saving space by not showing the IDs. The problem with Measures is that they can't be added as rows (which is the prerequisite for expanding/collapsing) - they can only be added under Values:
