Extrct nth value of a column using dax

[Fariba1984]

Hi experts,

I have this code, which calculate AVG of Top 3 of values of a column:

3%Quantile = 
var TopNRows  = TOPN(3,Table,[Column1])
return 
AVERAGEX(TopNRows,[Column1])

But I would like to extract  the 3th value of [Column1], after sorting on base of [Column1], how can I do that?  

[AntrikshSharma]

@Fariba1984  Here is an example of ranking alphabeticaly, you can try to adopt it for your own measure/table, since this returns 1 value it can be used in a measure, if you want multiple values then you will have to use CONCATENATEX

Fariba = 
VAR RankedTable =
    ADDCOLUMNS (
        VALUES ( Products[Brand] ),
        "@Rank",
            VAR CurrentBrand = Products[Brand]
            VAR BrandsGreaterThanCurrentBrand =
                FILTER ( VALUES ( Products[Brand] ), Products[Brand] <= CurrentBrand )
            VAR Result =
                COUNTROWS ( BrandsGreaterThanCurrentBrand )
            RETURN
                Result
    )
VAR N = 3
VAR TargetBrand =
    FILTER ( RankedTable, [@Rank] = N )
VAR Result =
    SELECTCOLUMNS ( TargetBrand, "Brand", [Brand] )
RETURN
    Result

For multiple values try this:

Fariba_ =
VAR RankedTable =
    ADDCOLUMNS (
        VALUES ( Products[Brand] ),
        "@Rank",
            VAR CurrentBrand = Products[Brand]
            VAR BrandsGreaterThanCurrentBrand =
                FILTER ( VALUES ( Products[Brand] ), Products[Brand] <= CurrentBrand )
            VAR Result =
                COUNTROWS ( BrandsGreaterThanCurrentBrand )
            RETURN
                Result
    )
VAR TargetBrand =
    FILTER ( RankedTable, [@Rank] IN { 1, 4, 5, 6 } )
VAR Result =
    CONCATENATEX ( TargetBrand, Products[Brand], ", " )
RETURN
    Result

[dedelman_clng · Accepted solution]

Hi @Fariba1984  - try this measure

3rd Value =
FILTER (
    ALLSELECTED ( Table[Column1] ),
    RANKX ( ALLSELECTED ( Table[Column1] ), Table[Column1],, DESC ) = 3
)

//****SWITCH "DESC" to "ASC" if you want 3rd lowest

Hope this helps

David

[Fariba1984]

Thnak you

Is it possible to implement it without ALLSELECTED, because with ALLSELECTED give me the wrong resault

[dedelman_clng]

@Fariba1984 - depends on how you are trying to use/display the value. Can you give more details or share a sample pbix?

[Fariba1984]

So I try to explain it in another way. I have defined a measure "Top 3" as you suggested me. But these measure give me back the 3th value of the whole table which is 0.8

As You see in the marked row, for each row I have 5 number of values --> "AnzahlTag".

Hier you can see what is these five values and which value I would like to extract

If I you need more information please let me know

[dedelman_clng]

@Fariba1984 - you can try replacing "ALLSELECTED" with "VALUES". Beyond that you can try @AntrikshSharma 's suggestion or provide a pbix.  It is hard to know what might be causing it not to work without knowing the underlying data and model.

[Fariba1984]

@dedelman_clng after changing ALLSELECT to VALUES it works as I expected for me. But could you tell what was the reason and problem with ALLSELECT?

[dedelman_clng]

@Fariba1984 - can't say for certain, but probably has to do with your model and how you are deploying the result.  On a simple card with a one table model, ALLSELECTED and VALUES gives the same result. With your matrix visual and likely more complicated model, there is a difference. I probably should've started with VALUES.

This article talks about ALLSELECTED and where it should and shouldn't be used: https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/ 

David