Get value from specific column and row number

[Marcel1989]

Hello,

let's have a table "table1" like

Title	Text	Site
Title 1	Text 1	Site A
Title 2	Text 2	Site A
Title 3	Text 3	Site A
Title 4	Text 4	Site B
Title 5	Text 5	Site B

I need a measure that gives me the value from the column "Title" for a specific row number. The row number should be hard coded into the measure. Even it might be sound strange, but the order doesn't matter. Additionally I want to have a slicer for "Site" so that if I filter for Site="Site B" in my slicer and I use the measure for the first row, it gives me Measure(1)="Title 4".

I have tried the INDEX function and TOPN, but I couldn't get it work. Hope you can help me.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

ROWNUMBER function (DAX) - DAX | Microsoft Learn

Expected result measure: =
VAR _rownumber = 1
VAR _t =
    FILTER (
        ADDCOLUMNS (
            'Data',
            "@rownumber",
                ROWNUMBER (
                    SUMMARIZE ( 'Data', 'Data'[Title], 'Data'[Text], 'Data'[Site] ),
                    ORDERBY ( 'Data'[Title], ASC, 'Data'[Text], ASC ),
                    DEFAULT,
                    PARTITIONBY ( 'Data'[Site] )
                )
        ),
        [@rownumber] = _rownumber
    )
RETURN
    CONCATENATEX ( _t, Data[Title], ", " )

[divergent]

Hi, 
It doesn't seem to work when I have a large database, as it keeps asking me to add argument in summarize function. 

Is there a way, I can extract a specific value from a column correponding to row number 1 and show that as a card visual

[Marcel1989]

Hi Jihwan_Kim,

thank you so much, it works. 🙂

I needed a few minutes to understand the solution and learned something new. Thanks!