topic Re: DAX formula for product mentioned most under a store in DAX Commands and Tips

DAX formula for product mentioned most under a store

Jay2022 — Mon, 21 Jul 2025 08:14:42 GMT

data looks kind of like this

store product

a 1

b 2

c 1

a 1

a 2

I want to be able to retrieve the product mentioned most under a,b,c etc

Re: DAX formula for product mentioned most under a store

FBergamaschi — Mon, 21 Jul 2025 08:35:38 GMT

I insteretd this data into a table

and this is the result I get

this si the DAX measure code

Most Mentioned =

VAR StoreProduct =

SUMMARIZE ( Tabella, Tabella[Store], Tabella[Product] )

VAR StoreProductMentions =

ADDCOLUMNS (

StoreProduct,

"@Mentions",

VAR ProductCurrentlyIterated = Tabella[Product]

RETURN

CALCULATE ( COUNTROWS ( Tabella ), Tabella[Product] = ProductCurrentlyIterated )

)

VAR StoreProductMentionsMostMentions =

ADDCOLUMNS (

StoreProductMentions,

"@MostMentions",

MAXX (

StoreProductMentions,

[@Mentions]

)

VAR Result =

CONCATENATEX(

FILTER (

StoreProductMentionsMostMentions,

[@Mentions] = [@MostMentions]

Tabella[Product], " / "

)

RETURN Result

As you see it might be there are multiple products with the max mentions, I consdiered that possibility in the code

I suggest anyway to use a data model including store and product dimensions

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: DAX formula for product mentioned most under a store

Jihwan_Kim — Mon, 21 Jul 2025 08:46:06 GMT

Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

INDEX function (DAX) - DAX | Microsoft Learn

product mentioned most: = VAR _t = ADDCOLUMNS ( SUMMARIZE ( data, store[store], 'product'[product] ), "@count", CALCULATE ( COUNTROWS ( data ) ) ) VAR _index = SUMMARIZE ( INDEX ( 1, _t, ORDERBY ( [@count], DESC ),, PARTITIONBY ( store[store] ) ), store[store], 'product'[product] ) VAR _list = SUMMARIZE ( CALCULATETABLE ( data, TREATAS ( _index, store[store], 'product'[product] ) ), store[store], 'product'[product] ) RETURN CONCATENATEX ( _list, 'product'[product], ", " )

Re: DAX formula for product mentioned most under a store

speedramps — Mon, 21 Jul 2025 09:07:58 GMT

Try this ...

Create some test data

Create a measure

Rank = var cursor_store = SELECTEDVALUE(Facts[Store]) RETURN RANKX( FILTER( ALL('Facts'), 'Facts'[Store] = cursor_store ), CALCULATE(COUNTROWS(Facts)), , ASC , DENSE )

Draw a table visual with FILTER in the FILTER PANE

Note that at store C noth Apples and Pears both had the top rank.

Please click thumbs up because I have tried to help.

Then click [accept solution] if it works.

Learn more about RANKX here ...

Re: DAX formula for product mentioned most under a store

Jay2022 — Mon, 21 Jul 2025 09:16:51 GMT

can't seem to get that working what does the DENSE bit at the end do ?

Re: DAX formula for product mentioned most under a store

Jay2022 — Mon, 21 Jul 2025 09:17:52 GMT

This is great thank you, can you tell me what i would add to count the results of the most popular/mentioned please ?

Re: DAX formula for product mentioned most under a store

Jay2022 — Mon, 21 Jul 2025 09:18:38 GMT

This is great thank you, can you tell me what i would add to count the results of the most popular/mentioned please ?

Re: DAX formula for product mentioned most under a store

FBergamaschi — Mon, 21 Jul 2025 09:52:43 GMT

Jay2022 is my solution not working?

Re: DAX formula for product mentioned most under a store

speedramps — Mon, 21 Jul 2025 10:49:55 GMT

Please read the traning materila I provided about RANX whichj explain how the Dense argument works.

Sometimes you find some products rank the same for a store.
For example Appes and Orageges may both rank 2nd place for a store.

The Dense help you deecide if you want to skip postion 3 and go to 4 for the next rank.

In your case it does not matter because you omly want the top rank.

Learn more about RANKX here ...

Re: DAX formula for product mentioned most under a store

govind_021 — Tue, 29 Jul 2025 10:34:10 GMT

Hey speedramps
try this , its short code

Most Mentioned Product_ =

VAR ProductCounts =

ADDCOLUMNS(

VALUES(Data[Product]),

"ProductCount", CALCULATE(COUNTROWS(Data))

)

VAR MaxCount =

MAXX(ProductCounts, [ProductCount])

VAR TopProducts =

FILTER(

ProductCounts,

[ProductCount] = MaxCount

)

RETURN

CONCATENATEX(

TopProducts,

'Data'[Product],

", "

)

Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalyst