Count Strings and return top 1 from a measure with Switch()

[Anonymous]

Hi all 

I have a problem with calculating max occurence of a string from measure Winner

there are 3 option : CONS STAT and EQUAL, based on Delta measure

Is it possible to return a winer over entire period ? (top 1 of occurences from Winner measure) 

[v-jianboli-msft]

Hi @Anonymous ,

Maybe you can use SUMMARIZE() function, it returns a summary table for the requested totals over a set of groups.

For more details, please refer to:

SUMMARIZE function (DAX) - DAX | Microsoft Learn

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I have created a simple sample:

Please try:

Measure = 
VAR _a =
    ADDCOLUMNS ( 'Cal Year', "Winner", [Winner] )
VAR _b =
    ADDCOLUMNS (
        { "CONS", "EQUAL", "STAT" },
        "Count", COUNTX ( FILTER ( _a, [Winner] = EARLIER ( [Value] ) ), [Winner] )
    )
VAR _c =
    MAXX ( _b, [Count] )
VAR _d =
    MAXX ( FILTER ( _b, [Count] = _c ), [Value] )
RETURN
    "Winner: " & _d & "  Count: " & _c

Final output:

Best Regards,

Jianbo Li

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

[Anonymous]

Thank you!! works perfectly:)

would it be possible to pass dynamic table into _b variable? instead of {"CONS","STAT","EQUAL"} having a distinct values from "Winner" column from _a variable? 

[amitchandak]

@Anonymous , If winner is a measure, first you have to do dynamic segmentation and then try for TOPN of rank

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

[Anonymous]

Hi, thanks for reply,

I had made dynamic segmentation and it works but only if i sum winner over a calendar,

when i want to count winner over a product it seems im missing a relation in a measure

My current segment:

Measure

and the output

now i need to make matrix table with my products in rows and Scenario (cons, stat,equal) as columns

this seems like im missing the relation between my measure and the material table. I've tried to change a measure by replacing countrows(cal year) with countrows(fact_table - which has material column) but the query is too big to calculate. Same issue when i use virtual table...is there a simple way to calculate the segment over date by each material?