Forum Discussion

magnusks's avatar
magnusks
Icon for Helper I rankHelper I
2 years ago
Solved

Count most common text in column and display value

I have a table that looks something like this: 

 

Table name: Purchases

Member IDPurchased

Member IDPurchased
1Car
2Car
3House
4Car
5House
6Car
7Car

 

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

6 Replies

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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! 🤠

     

    • Daniel29195's avatar
      Daniel29195
      Icon for Community Champion rankCommunity Champion
      2 years ago

      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! 🤠

       

       

    • magnusks's avatar
      magnusks
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Daniel29195
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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. 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.