Forum Discussion

Sixian's avatar
Sixian
Frequent Visitor
5 years ago

How to create a view with the largest three values for each row

I'm struggling with a couple of problems in the same project and I'll post two different help request.

You can find the Problem 1 in this post: https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-filter-a-page-with-different-attributes-using-the-same/m-p/1613382#M32457

 

Problem 2.

I have a table with more than 80 Attributes, like this:

I want to create a page with a view that shows the largest three attributes for each name, like this:

 

I would really appreciate any help.

Thank you

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    firstly, you need to unpivot your data like this

    then add a dimension table like this

    then create two measures

    Attr = CALCULATE(MAX('Table'[Attribute]),FILTER(VALUES('Table'[Attribute]),RANKX(ALL('Table'[Attribute]),CALCULATE(MAX('Table'[Value])))=MAX('TopN'[Index])))
Val = CALCULATE(MAX('Table'[Value]),FILTER(VALUES('Table'[Attribute]),RANKX(ALL('Table'[Attribute]),CALCULATE(MAX('Table'[Value])))=MAX('TopN'[Index])))

    then show the view you want 

     

    • Sixian's avatar
      Sixian
      Frequent Visitor
      5 years ago

      My Data Table is a calculated table where I added all the Attributes columns using formulas. How can I unpivot the table?

  • Sixian's avatar
    Sixian
    Frequent Visitor
    5 years ago

    I still can't find a way to unpivot my Attributes Table.

    I can't do it in power query, because it is a calculated table, created using the following DAX code:

    AttributesTable =
    var filteredTable = FILTER('InputTable', 'InputTable'[Exclude] = "NO")
    return SELECTCOLUMNS(filteredTable,
    "Name", 'InputTable'[Name]
    )

     

    Then, all the more than 80 attributes have been added as calculated columns using DAX code.

    Please, any help?

    Thank you