Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: How to return the rows that equal the current row (for a calculated column)

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to return the rows that equal the current row (for a calculated column)


    How to return the rows that equal the current row (for a calculated column)
    Hello,
    My end goal is to create a sortby column as shown here. (so that I can sort [school] by ... [sortby])
    I need the formula for the "sortby" calculated column.
    For named "Years"
    It should be something like: =MIN(Years[grade])
    where years[school] = years[school] OF THIS ROW. <=???




    Thanks!
    Alex

    *in powerbi i get this error if I use [grade] for the sort by column
    Sort By Another Column Error

    We cannot sort the 'school' column by 'grade'. You can't have more than one value in 'grade' for the same value in 'school'. Choose a different column for sorting or update the data in 'Year'.

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return the rows that equal the current row (for a calculated column)

    Hi Alex,
    Simplest way I can think of as a DAX calculated column is:
    Code:
    = CALCULATE ( MIN ( years[grade] ), ALLEXCEPT ( years, years[school] ) )

  3. #3
    New Member
    Join Date
    Sep 2014
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return the rows that equal the current row (for a calculated column)

    Thanks Owen,
    That gives me the column i was looking for.......
    unfortunately, when i try to use it as the "sort by column", power bi complains about a circular dependency. "A circular dependency was detected:"

  4. #4
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return the rows that equal the current row (for a calculated column)

    I see...
    I get the same error in Power Pivot for Excel.

    In Power BI Desktop I get a different message, but I guess it's the same problem:
    Sort By Another Column Error
    This column can't be sorted by a column that is already sorted, directly or indirectly, by this column.

    So it appears that you can't sort by a calculated column whose DAX expression depends on the column you want to sort.

    I think you'll just have to handle this at the ETL stage, say in Power Query.
    There are several ways you could replicate the behaviour of the above DAX expression in Power Query (making no assumptions about sort order of rows) such as:
    Code:
    = Table.AddColumn(
          PreviousStep,
          "sortby",
          each let CurrentSchool = [school] in List.Min( Table.SelectRows(PreviousStep, each [school] = CurrentSchool )[grade] )
    )

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return the rows that equal the current row (for a calculated column)

    Cheers for the PowerQuery line, that's great. I'll add that to my example notebook.
    In fact, I did as you mentioned and added some sorting info in my date(aka, grade) table.

    Thanks Owen!

    PS, Say hi to StephenG if you run across him ( he's a common contact between us in LinkedIn. )

  6. #6
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return the rows that equal the current row (for a calculated column)

    No worries
    Well whaddaya know, small world, I will say hi

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •