topic Re: Count Strings and return top 1 from a measure with Switch() in DAX Commands and Tips

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

Anonymous — Fri, 28 Oct 2022 13:05:15 GMT

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)

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

amitchandak — Sat, 29 Oct 2022 03:27:36 GMT

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

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

v-jianboli-msft — Mon, 31 Oct 2022 08:19:29 GMT

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.

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

Anonymous — Wed, 02 Nov 2022 08:49:36 GMT

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?

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

v-jianboli-msft — Wed, 02 Nov 2022 08:53:35 GMT

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.

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

Anonymous — Mon, 05 Dec 2022 07:23:11 GMT

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?