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)
Solved! Go to 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.
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.
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.
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?
@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
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?