topN DAX issue

Question

Hello All,I have input as shown in the following table. My expection is expected column in the table but getting is the final outcome.getting = CALCULATE( SUMX(TOPN(2, table1, table1[score]),table1[plano]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALLEXCEPT(table1, table1[shop id],table1[visit id],table1[score])&nbsp;shop idvisit iddatescoreplanoexpectedgetting1115/07/20201122128/07/20200.33022131/07/202011222421/07/20200.671232515/07/20200.67123267/07/20200.67123&nbsp;Requirement:I want to get top two values from plano column based on top two scores. In case when all the scores are same, it is giving me wrong answer (it is adding all 3). I sorted my columns in Query editor shop id, scores and visit date. I want the ouput of latest date if two dates having same score.&nbsp;Can anyone help me with this.Thanks

greg_deckler · Answer

Minakshi&nbsp;- Yeah, TOPN and RANKX are going to be problematic in this regard because of how they handle duplicates. I had to jump through some hoops like this when I developed TRIMMEAN. Check that out and see if the technique used there helps. It's essentially a "while" loop.
&nbsp;
https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/m-p/1074075#M504

amitchandak · Answer

Minakshi , Use Rank Tie- breaker technique discussed in 
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
&nbsp;
For Rank Refer these linkshttps://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columnshttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

anonymous · Answer

Hi Minakshi ,According to my understanding, you want to calculate top2 sum of plano based on ranking by multi columns, right?&nbsp;You could use the following formula:IndexColumn =
VAR a = [shop id]
VAR b = [score]
VAR d = [visit id]
VAR t1 =
    FILTER ( ALL ( 'table1' ), table1[shop id] = a )
VAR t2 =
    FILTER ( ALL ( 'table1' ), table1[shop id] = a &amp;&amp; 'table1'[score] = b )
VAR t3 =
    FILTER (
        ALL ( 'table1' ),
        table1[shop id] = a
            &amp;&amp; 'table1'[score] = b
            &amp;&amp; 'table1'[visit id] = d
    )
RETURN
    RANKX (
        t1,
        RANKX ( t1, [score],, DESC, SKIP ) * 100
            + RANKX ( t2, [visit id],, ASC, SKIP ) * 10
            + RANKX ( t3, [date],, ASC, SKIP ),
        ,
        ASC,
        SKIP
    )expected =
CALCULATE (
    SUM ( table1[plano] ),
    FILTER (
        ALL ( table1 ),
        ( table1[IndexColumn] = 1
            || table1[IndexColumn] = 2 )
            &amp;&amp; 'table1'[shop id] = MAX ( table1[shop id] )
    )
)My visualization looks like this:Is the result what you want? If you have any questions, please upload some data samples and expected output.Please do mask sensitive data before uploading.&nbsp;Best Regards,Eyelyn Qin

minakshi · Answer

Anonymous&nbsp;,&nbsp;amitchandak&nbsp;,&nbsp;Greg_Deckler&nbsp; Thanks everyone. I got the required result by putting date into TOPN selection.Thanks once again.

anonymous · Answer

Hi Minakshi&nbsp;,Did I answer your question? Please mark my post as a solution, thank you~

Forum Discussion

topN DAX issue

5 Replies

Related content

TOPN DAX function Issue

DAX Issue

DAX code issue

DAX issue ISBLANK

Basic DAX Issue

shop id	visit id	date	score	plano	expected	getting
1	1	15/07/2020	1	1	2	2
1	2	8/07/2020	0.33	0	2	2
1	3	1/07/2020	1	1	2	2
2	4	21/07/2020	0.67	1	2	3
2	5	15/07/2020	0.67	1	2	3
2	6	7/07/2020	0.67	1	2	3