cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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) 

win_count.JPGwinnerDAX.JPG

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

vjianbolimsft_0-1667204288105.png

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:

vjianbolimsft_1-1667204325035.png

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.

View solution in original post

5 REPLIES 5
v-jianboli-msft
Community Support
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.

v-jianboli-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

vjianbolimsft_0-1667204288105.png

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:

vjianbolimsft_1-1667204325035.png

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? 

amitchandak
Super User
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

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:

MattU1_1-1670188841217.png

Measure

MattU1_2-1670188908291.png

and the output

MattU1_0-1670188794724.png

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

MattU1_3-1670189247819.png

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?

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors