Count most common text in column and display value

[magnusks]

I have a table that looks something like this: 

Table name: Purchases

Member IDPurchased

Member ID	Purchased
1	Car
2	Car
3	House
4	Car
5	House
6	Car
7	Car

In this case there are 5 cars and 2 houses.

I want to make a write a measure in DAX so I can display on a card the most frequently purchased item and the number of times that item have been purchased. In this case: 5 cars.

Thanks

[Anonymous · Accepted solution]

Hi @magnusks ,

Create a measure, get the maximum number and its name.

Measure = 
var _table=
SUMMARIZE('Table',[Purchased],"Count",COUNTX(FILTER(ALL('Table'),'Table'[Purchased]=MAX('Table'[Purchased])),[Purchased]))
var _table2=
FILTER(
    _table,[Count]=MAXX(_table,[Count]))
return
MAXX(_table2,[Purchased])&" "&MAXX(_table2,[Count])

The final result obtained is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

^{Best Regards,}

^{Clara Gong}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[magnusks]

Thank you Clara. This worked 🙂

[Daniel29195]

@magnusks 

output : 

measure

Measure 8 = 
var ds = 
TOPN(1,
ADDCOLUMNS(
    VALUES('count'[Purchased]),
    "x", CALCULATE(COUNTROWS('count')
    )
),
[x],
DESC
)

var n =  SELECTCOLUMNS(ds,'count'[Purchased])
var nn =  SELECTCOLUMNS(ds,[x])

return

n & ":" & nn

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[magnusks]

Thanks Daniel. I tried it but there seems to be something wrong in: 

var n =  SELECTCOLUMNS(ds,'count'[Purchased])

It doesnt "allow" me to insert the Purchased column from the Count-table. Any suggestions? 🙂

Cheers

[Daniel29195]

@magnusks 

the table name is count  from my side. 

yours i assume is different. 

you need to change the 'count' with the table_name which you have on your side. 

[Daniel29195]

@magnusks 

to have 5 cars  : 

change the last line to : nn & " " & n

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠