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 column to count the text duplicates in a specific order, that it looks to the previous rows of data of the column. See data example below:

ColumnA  ColumnB

A1
B1
C1
D1
A2
E1
F1
B2
A3
D2
G1
H1
A4

 

How can i create calculated column B with DAX? 

 

Thanks for your help!

 

Kind regards,

 

Bas Kamphuis

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

5 Replies

  • Baskar's avatar
    Baskar
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    very simple dude.

     

    drag your column A in  and Drag Column B too.

     

    In column B change the default summarization to count . Thats it .

     

     

    or do u want any measure , we can ctreate it .

     

    Measure = count(column B)

     

    Thats it, 

     

    let me know if any help

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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