Forum Discussion

KamphuisB's avatar
KamphuisB
Frequent Visitor
9 years ago
Solved

Count text values in column +1 DAX formula

Dear Power BI Users,   I am a bit new to the DAX formulas. I have searched a lot on the forum but i haven't found my answer for the following question.    I would like to create a calculated colu...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi KamphuisB,

    For your requirement, you’d better add a index column, and calculate the result based on the index column. I reproduce your scenario and get desired result as follows.

    Click the table, right click->Edit Query. In the Power Query Editor, under Add column, click add index column highlighted in yellow. You will get the index from first rows shown in following screenshot.



    Then create another calculated column using the formula below.

    ColumnB = RANKX(FILTER(Test1,Test1[ColumnA]=EARLIER(Test1[ColumnA])),Test1[Index],,ASC)




     

     

    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia

KamphuisB's avatar
KamphuisB
Frequent Visitor
9 years ago

Hi Angelia,


Thanks for answering my question. it worked with a calculated column!

 

I have one question: Is it also possible to make the RANKX column in the query editor? Then i get an expression error RANKX not recognised.

 

With kind regards,

 

Bas Kamphuis

KamphuisB's avatar
KamphuisB
Frequent Visitor
9 years ago

Hi Angelia,

 

I have the results that i wanted. The reason i asked the question above was that i needed a table with only the results of the RANKX =1 column. Since you can only really filter out the data inside the query editor, i made an New table with FILTER('Table','Table'[RANKX COLUMN] = 1)

 

 

With kind regards,

 

Bas Kamphuis

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    HIi KamphuisB,

    I am very glad you have resolved your issue. If you have any other issue, please feel free to ask in this forum.

    Best Regards,
    Aneglia