cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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)

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

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

``````Measure =
VAR _a =
ADDCOLUMNS ( 'Cal Year', "Winner", [Winner] )
VAR _b =
{ "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.

5 REPLIES 5
Community Support

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.

Community Support

Hi @Anonymous ,

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

``````Measure =
VAR _a =
ADDCOLUMNS ( 'Cal Year', "Winner", [Winner] )
VAR _b =
{ "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
Not applicable

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?

Super User

@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
Not applicable

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.