CODEWITHSUNDEEP
powerbidaxtop-n
courty340
courty340

Reputation: 131

PBI DAX Query for Top N to return Text

I am trying to write a DAX query to return the Vendor Name (data type is text) with the highest sales. I have a query that returns the sales amount for the vendor with the highest sales, but I need the opposite of this. I have tried just doing the inverse of what I have here, but it does not work. I know there is a simple solution here, but cannot figure out how to get what I need.

Top Air Vendor = VAR 
    RankingContext = VALUES ('Dim Vendor'[Vendor Name])
    RETURN
           CALCULATE('Fact Invoice'[Air Volume],
           TopN(1,All('Dim Vendor'[Vendor Name]),
           [Air Volume]),
           RankingContext)

I also tried it using:

Top Air Vendor = Calculate ( Selectedvalue ('Dim Vendor'[Vendor Name] ),
    TopN (1, ('Fact Invoice'), [Air Volume], Desc) )

which does return an airline name, but something in the ranking is wrong. It's returning the third highest value but I cannot figure out why.

I also have similar queries that work just fine such as:

Top Air Vendor Tickets % = VAR 
    RankingContext = VALUES ('Dim Vendor'[Vendor Name])
    RETURN
        CALCULATE('Fact Invoice'[Air Tickets],
            TopN(1, ALL('Dim Vendor'[Vendor Name]), [Air Tickets]),
            RankingContext) / [Air Tickets]

Upvotes: 1

Views: 1258

Answers (1)

courty340
courty340

Reputation: 131

Was able to solve it by simplifying the DAX code a bit:

Top Air Vendor = TOPN(1,ALLNOBLANKROW('Dim Vendor'[Vendor Name]),
      'Fact Invoice'[Air Volume],desc)

Upvotes: 1

Related Questions