DAX formula for product mentioned most under a store

[Jay2022]

data looks kind of like this 

store product           

a        1

b        2

c         1

a        1

a         2

b

c

b

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

[FBergamaschi · Accepted solution]

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

[Jihwan_Kim · Accepted solution]

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], ", " )

[govind_021]

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

[speedramps · Accepted solution]

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 ...

• https://learn.microsoft.com/en-us/dax/rankx-function-dax
• https://www.youtube.com/watch?v=z2qzJVeYhTY
• https://www.youtube.com/watch?v=eb_-i_hWSDI

[speedramps]

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 ...

• https://learn.microsoft.com/en-us/dax/rankx-function-dax
• https://www.youtube.com/watch?v=z2qzJVeYhTY
• https://www.youtube.com/watch?v=eb_-i_hWSDI

[Jay2022]

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

[Jihwan_Kim · Accepted solution]

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], ", " )

[Jay2022]

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

[Jay2022]

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

[FBergamaschi · Accepted solution]

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

[FBergamaschi]

@Jay2022 is my solution not working?