Forum Discussion

Marcel1989's avatar
Marcel1989
Frequent Visitor
2 years ago
Solved

Get value from specific column and row number

Hello,

 

let's have a table "table1" like

 

TitleTextSite
Title 1Text 1Site A
Title 2Text 2Site A
Title 3Text 3Site A
Title 4Text 4Site B
Title 5Text 5Site 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's avatar
    Jihwan_Kim
    2 years ago

    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], ", " )

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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], ", " )

     

    • Marcel1989's avatar
      Marcel1989
      Frequent Visitor
      2 years ago

      Hi Jihwan_Kim,

       

      thank you so much, it works. 🙂

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

    • divergent's avatar
      divergent
      Frequent Visitor
      2 years ago

      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