Need a DAX to calculate the Max of string value count

[1967Maven]

I plan to use a column bar chart to visualize data in the below table in such away that when the Satisfaction_Score is on X Axis, the Y Axis should show ONLY that Satisfaction_Factor which has that max count for that particular Satisfaction_Score (in some cases there might be more than 1 Satisfaction_Factor). 
Help me with a DAX to do this calculation. 

Customer_ID	Group	Satisfaction_Score	Age	Gender	Location	Purchase_History	Support_Contacted	Loyalty_Level	Satisfaction_Factor	Latitude	Longitude
81-237-4704	A	6	55	Male	Phoenix.AZ	Yes	No	Low	Price	33.4484	-112.074
14-117-0504	A	7	46	Female	Los Angeles.CA	Yes	No	Low	Product Variety	34.0522	-118.244
21-336-6416	A	7	55	Female	Houston.TX	Yes	Yes	Low	Packaging	29.7604	-95.3698
59-781-3650	A	1	42	Male	Los Angeles.CA	Yes	No	Medium	Price	34.0522	-118.244
52-712-5734	B	9	37	Male	Austin.TX	Yes	Yes	Medium	Packaging	30.2672	-97.7431
13-044-3048	B	9	52	Female	Philadelphia.PA	Yes	No	Medium	Packaging	39.9526	-75.1652
13-138-2725	B	9	60	Male	Los Angeles.CA	No	No	Low	Ease of Use	34.0522	-118.244
96-029-4763	B	8	28	Female	Phoenix.AZ	Yes	No	Low	Product Variety	33.4484	-112.074
74-526-1211	B	3	32	Female	Austin.TX	No	Yes	High	Ease of Use	30.2672	-97.7431
66-506-4265	A	10	50	Female	Austin.TX	No	Yes	Low	Delivery Speed	30.2672	-97.7431
53-279-2688	B	7	40	Male	Austin.TX	Yes	No	Low	Features	30.2672	-97.7431
65-115-5317	B	10	49	Female	Chicago.IL	Yes	Yes	Low	Price	41.8781	-87.6298
61-799-2819	A	5	25	Male	New York.NY	No	Yes	Medium	Product Quality	40.7128	-74.006
14-895-1819	B	7	55	Male	Houston.TX	Yes	No	High	Product Quality	29.7604	-95.3698
04-730-7617	B	9	35	Male	New York.NY	No	No	High	Ease of Use	40.7128	-74.006
70-262-1283	A	2	60	Male	New York.NY	No	Yes	Medium	Price	40.7128	-74.006
13-111-3439	A	4	53	Female	Phoenix.AZ	No	No	Low	Ease of Use	33.4484	-112.074
31-562-2114	A	5	44	Female	Philadelphia.PA	No	No	High	Customer Service	39.9526	-75.1652
09-982-4228	A	2	28	Male	Philadelphia.PA	No	Yes	Medium	Features	39.9526	-75.1652
89-372-9394	A	2	47	Female	Dallas.TX	Yes	Yes	Low	Ease of Use	32.7767	-96.797
31-456-9342	B	8	37	Female	Houston.TX	No	No	High	Brand Reputation	29.7604	-95.3698
23-653-2977	A	2	31	Female	Phoenix.AZ	Yes	No	Medium	Brand Reputation	33.4484	-112.074
73-020-4731	B	5	48	Male	Phoenix.AZ	No	No	High	Price	33.4484	-112.074
60-913-7924	B	9	52	Female	Houston.TX	Yes	Yes	Low	Features	29.7604	-95.3698
31-778-0358	A	4	57	Male	Chicago.IL	Yes	Yes	High	Features	41.8781	-87.6298
67-157-3066	A	1	55	Female	San Antonio.TX	Yes	No	Medium	Support Availability	29.4241	-98.4936
17-203-6948	B	1	51	Female	Austin.TX	No	No	Low	Delivery Speed	30.2672	-97.7431

[v-venuppu]

Hi @1967Maven ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @1967Maven ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MasonMA @Jihwan_Kim @FBergamaschi for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[MasonMA]

@1967Maven 

Hi, another simpler approach you may consider would be (I named the table with your sample data 'CustomerFeedback')

1. Create a new table as below. 

FactorCountSummary = 
SUMMARIZE(
    'CustomerFeedback',
    'CustomerFeedback'[Satisfaction_Score],
    'CustomerFeedback'[Satisfaction_Factor],
    "FactorCount", COUNTROWS('CustomerFeedback')
)

 2. Create one new column 'MaxCountPerScore' in this table. This is to give you maximum FactorCount within that Satisfaction_Score.

MaxCountPerScore = 
CALCULATE(
    MAX(FactorCountSummary[FactorCount]),
    ALLEXCEPT(FactorCountSummary, FactorCountSummary[Satisfaction_Score])
)

 3. Create another new 'Flag' Column used as filter/slicer for your visual. and the table will look like this,

ShowOnlyTopFactors = 
IF(
    FactorCountSummary[FactorCount] = FactorCountSummary[MaxCountPerScore],
    1,
    0
)

4. Set up the Culumn bar chart as below

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file. In the question, I saw the request about satisfaction factor and score, so I only created dimension tables for factor and score.

INDEX function (DAX) - DAX | Microsoft Learn

count: = 
COUNTROWS(customer_fact)

MAX count: = 
VAR _t =
    SUMMARIZE (
        ALL ( customer_fact ),
        Satisfaction_Score[Satisfaction_Score],
        Satisfaction_Factor[Satisfaction_Factor]
    )
VAR _max =
    INDEX (
        1,
        _t,
        ORDERBY ( [count:], DESC ),
        ,
        PARTITIONBY ( Satisfaction_Score[Satisfaction_Score] )
    )
VAR _maxcountfactorlist =
    SUMMARIZE ( _max, Satisfaction_Factor[Satisfaction_Factor] )
VAR _maxcount =
    CALCULATE (
        [count:],
        Satisfaction_Factor[Satisfaction_Factor] IN _maxcountfactorlist
    )
RETURN
    IF ( [count:] = _maxcount, [count:] )

[FBergamaschi]

Not sure this could be OK, using tooltips to see the list of the top count factors, in a bar chart

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]

Hi, here the DAX code in a table

I imported your table and named it "Tabella"

Then created the following measures

Count = COUNTROWS( Tabella )

Max Count for Factors =

MAXX (

    ADDCOLUMNS(

        VALUES( Tabella[Satisfaction_Factor] ),

        "@Count", [Count]

    ),

    [@Count]

)

Max Count Factors =

VAR FactorsAndCount =

 ADDCOLUMNS(

    VALUES( Tabella[Satisfaction_Factor] ),

    "@Count", [Count]

)

VAR MAXVALUE =

MAXX (

FactorsAndCount,[@Count]

)

RETURN

CONCATENATEX(

    FILTER (

        FactorsAndCount,

        [@Count] = MAXVALUE

    ),

    Tabella[Satisfaction_Factor], " / "

)

Result

I am not sure to to show something like this in a bar chart as the list of max count factors are texts, maybe you want to draw something to let me understand better ?

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