locked
CountRows in a table RRS feed

  • Question

  • Dear All, 

    i am a newbie on Power BI and I am facing a trivial problem. I have created the following table which enlisted the type of fault and the related number of calls. I would like to insert in that table a simple incremental index which say for instance the first row is 1 the second 2 and so on (look the snap for the data model and the table). This incremental index can allow me to filter the TOP 5 values in the table. I used RANKX but the problem is that for same value it give me as a tie the same number. I.E the 2nd and 3rd rows have both rank 2. 

    I have 2 tables 

    Query1 with fields: CallRef, CallType, Cause1, CompletionDate measures: FaultCount and ranx1

    LU_Cause_Code_Description fields: Cause_Code (key), Description

    Relationship LU_Cause_Code->Query1 is 1toN Cross Filter Direction: "Both"

    Table: 

    Description|FaultCount|rankx

    Rail 8 1

    Assy 1 2

    Other 1 2

    It seems an easy issue but for some reasons I do not get it. 

    The formula used are :

    FaultCount = COUNTROWS( DISTINCT(Query1[Call_Ref] ) )

    ranx1 = rankx(filter(all(LU_Cause_Codes[Description]),[FaultCount]),[FaultCount],,DESC,dense)

    Please help

    Best Regards

    Luca

    Thursday, September 10, 2015 10:02 AM

Answers

  • The easiest way to create an index in Power BI would be to use the built functionality to transform your data when you import it.

    You need to go to Edit Queries and then select the table where you want to add the index.

    Once the table is selected, go to the Add Column tab and it the ribbon you will see Add Index Column.

    This gives you the option to start an index from 0, 1, or a custom value.

    Make sure you sort your table properly before adding the index because it will start with the first row in your view and increment to the last in that order.

    With a few minor UI differences, this basically works the same in Excel using the Power Query add-in too.


    Thursday, September 10, 2015 9:05 PM
    Answerer
  • You can solve this with an index column, but be aware that index columns can take up a lot of space in the model, as they cannot be compressed by the engine. Typically around 30MB for a 2M row table (this is what I have seen).

    There is a DAX function TOPN to do exactly what you're looking for, and that deals with ties as well. 'Solving' ties by depending on the load order seems a bit arbitrary. 

    • Proposed as answer by Michael Amadi Friday, September 18, 2015 2:01 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:52 AM
    Tuesday, September 15, 2015 3:01 PM
    Answerer

All replies

  • The easiest way to create an index in Power BI would be to use the built functionality to transform your data when you import it.

    You need to go to Edit Queries and then select the table where you want to add the index.

    Once the table is selected, go to the Add Column tab and it the ribbon you will see Add Index Column.

    This gives you the option to start an index from 0, 1, or a custom value.

    Make sure you sort your table properly before adding the index because it will start with the first row in your view and increment to the last in that order.

    With a few minor UI differences, this basically works the same in Excel using the Power Query add-in too.


    Thursday, September 10, 2015 9:05 PM
    Answerer
  • You can solve this with an index column, but be aware that index columns can take up a lot of space in the model, as they cannot be compressed by the engine. Typically around 30MB for a 2M row table (this is what I have seen).

    There is a DAX function TOPN to do exactly what you're looking for, and that deals with ties as well. 'Solving' ties by depending on the load order seems a bit arbitrary. 

    • Proposed as answer by Michael Amadi Friday, September 18, 2015 2:01 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:52 AM
    Tuesday, September 15, 2015 3:01 PM
    Answerer