DAX "multiple columns" error when trying to return the Nth ranked text value

Question

I was working on a solution to calculate something similar to Qlikview's FirstSortedValue in DAX for Power BI.

To my knowledge, there is no solution out of the box to get the functionality of getting a text value (much like the category) which by its rank is in an nth position.

My question is: Why does this work on a single column, but returns a multiple column error when used on a table?

CALCULATE (
    SAMPLE (
        1,
        FILTER (
            'table_name',
            RANKX (
                ALL ( 'table_name'[column_name] ),
                CALCULATE ( COUNT ( 'table_name'[column_name] ) ),
                , , SKIP
            )
                = Nth
        ),
        TRUE ()
    )
)

a different variation as well:

CALCULATE (
    SAMPLE ( 1, VALUES ( 'table_name'[column_name] ), TRUE () ),
    FILTER (
        'table_name',
        RANKX (
            ALL ( 'table_name'[column_name] ),
            CALCULATE ( COUNT ( 'table_name'[column_name] ) ),
            , , DENSE
        )
            = Nth
    )
)

Both of these and many more of my attempts work when it is a single column, but why would this not work when used on a table with multiple columns? It should return a sample of 1 text value according to the Nth rank I choose? Akin to answering a question of "What is the Nth top selling product name according to salescount?".

I am stumped, still trying to find an answer. Firstnonblank is not an option, because I do not care for a solution to get the 1st ranked answer only; do not suggest that.

This question may be what you're looking for: https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column — Alexis Olson, Oct 22 '18 at 15:01
Hi, no maxx() and topn() are not valid for a re-usable ranked solution. Neither of those will return a 3rd ranked text value. — Vaidøtas I., Oct 22 '18 at 15:14

Alexis Olson · Answer 1 · 2018-10-22T19:26:20.573

1

The easiest way to get this to work from what you have is to make your table into a single column using SELECTCOLUMNS.

The following will give you a single column table with column name "Column Name".

SELECTCOLUMNS( FILTER( [...] ), "Column Name", 'table_name'[column_name] )

CALCULATE (
    SAMPLE (
        1,
        SELECTCOLUMNS(
            FILTER (
                'table_name',
                RANKX (
                    ALL ( 'table_name'[column_name] ),
                    CALCULATE ( COUNT ( 'table_name'[column_name] ) ),
                    , , SKIP
                )
                = Nth
            ),
            "Column Name",
            'table_name'[column_name]
        ),
        TRUE ()
    )
)

There may be a better way to tackle the question as a whole though, so please provide a minimal complete verifiable example of sample data and desired output if possible.

edited Oct 22 '18 at 19:26

answered Oct 22 '18 at 16:26

Alexis Olson

38,724
7
42
64

This answer would be useful if I were to understand how to implement the selectcolumns within the original formula. I am still going to look into it. +1 However I've worked out a (partial) solution myself. – Vaidøtas I. Oct 22 '18 at 18:58
You just wrap it around the `FILTER` as I showed. I'll edit in a more complete version. – Alexis Olson Oct 22 '18 at 19:23
You can up my chosen answer, posted below, because it fixes the entire issue and regardless of ties - even gives a concatenated result. – Vaidøtas I. Oct 24 '18 at 12:43

Vaidøtas I. · Accepted Answer · 2018-10-23T15:56:08.727

I have come up with a solution which works on multi-column tables with a caveat - ties, due to there being SAMPLE(1...) used in the formula are alphabetically favored:

SAMPLE(1, FILTER(ALL('X'[Name]), RANKX(ALL(X[Name]), CALCULATE(COUNT('X'[Name])),,,skip) = Nth), TRUE())

I will try to add a solution later to get ties ironed out with CONCATENATEX (which I've gotten to work) to be displayed in one go and therefore no stone will be left unturned.

EDIT: Oct 23 2018 Here it is! The final succinct version: works great!

Measure =

VAR Nth = [number] RETURN CONCATENATEX(DISTINCT('table_name'[column_name]), CALCULATE(FILTER(VALUES('table_name'[column_name]), RANKX(ALL('table_name'[column_name]), CALCULATE(COUNT('table_name'[column_name])),,,Dense) = Nth)),",")

DAX "multiple columns" error when trying to return the Nth ranked text value

2 Answers2