Share via

Formula to find the second different text in a column

pbiexpert 21 Reputation points
2021-01-28T22:25:04.163+00:00

Hey Folks,
I managed to find the first text value in a column with this dax formula:
firsttextvalue = TOPN(1,VALUES('table'[columnname])) --> TOPN because my table is already organized
what is the formula to find the second text value in the same column? I do not mean the second row value, because there is no information which row contains the next different text.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,798 questions
0 comments
Accepted answer
  1. Lz._ 8,991 Reputation points
    2021-01-29T07:15:49.357+00:00

    Hi @pbiexpert

    Assuming problem understood

    61774-demo.png

    SecondTextVal :=
    VAR FirstTextVal = [firsttextvalue] // Your existing measure
    RETURN
    CALCULATE (
    TOPN ( 1, VALUES ( 'table'[columnname] ) ),
    FILTER ( 'table', 'table'[columnname] <> FirstTextVal )
    )

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,821 Reputation points
    2021-01-29T06:01:52.92+00:00

    Hi,

    From the top function you used , did you mean that you want to find the second biggest value in that column ?

    If so,
    You could try :
    =MINX(
    TOPN(2,VALUES('table'[columnname]))
    )

    If you want to dig deep for Nth value in a column you could try more sophisticated solution like this : display the top N elements

    Regards,
    Lukas

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    What can I do if my transaction log is full?--- Hot issues November
    How to convert Profiler trace into a SQL Server table -- Hot issues November

  2. pbiexpert 21 Reputation points
    2021-01-29T15:37:00.713+00:00

    Many thanks for your fast reply! Yes @Lz._ , your approach is the winning one, it gave me back exactly the second different text that I was looking for. The formula MINX did not work, I bet it is because I have text in the column and MINX is used for determining values. Thanks a lot! :)