Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
SomeDataDude
Advocate I
Advocate I

How to rank sales on one line

Hello,

 

I need help formulating dax measure. The image below shows that for OrderID 1717694 the amount is 696.67. This is now shown on all 5 lines. However, I only want to see this on 1 line.

 

SomeDataDude_0-1662468600707.png

This is the output I am looking for:

SomeDataDude_1-1662469080426.png

 

In SQL I use the following code to organize this:

 

 

 

iif(
     RANK () 
      OVER (  
       PARTITION BY Table1.invkey 
        ORDER BY Table1.OrderID, Table2.ordsubtaskno, Table3.goodid DESC ) =1, 
          Table4.Amounttwv,0) 'TotalAmount',

 

 

 

How do i do this with dax. Or is this not possible?

I've already tried it with RankX and Rank.EQ. But probably I'm not applying it right.

1 ACCEPTED SOLUTION

Hi again, 

It will be something like this? 

onurbmiguel__0-1662642797400.png

 

the DAX for the new measure: 

 

TotaalAmount 2 = 
var _id = SELECTEDVALUE(Query1[OrderId])
var _control = 
    CALCULATE(
        min(Query1[DeliveryRealTime]),
        FILTER(ALL(Query1),Query1[OrderId]= _id))
return 
if(min(Query1[DeliveryRealTime]) = _control, [TotaalAmount], BLANK())

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


View solution in original post

4 REPLIES 4
onurbmiguel_
Super User
Super User

Hi SomeDataDude

Can you share some sample of your data, to understand how you have structure the information.

 

BR 

Bruno 

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Hi @onurbmiguel_ 

 

Thanks for replying. 

 

I have prepared an example file. In this example, I have merged multiple tables in Query1. The amount is taken from table Query2.

 

If you have any questions, let me know.

 

You can download the pbix file via the following link: https://we.tl/t-gDSbxD7qs5 

 

Hi again, 

It will be something like this? 

onurbmiguel__0-1662642797400.png

 

the DAX for the new measure: 

 

TotaalAmount 2 = 
var _id = SELECTEDVALUE(Query1[OrderId])
var _control = 
    CALCULATE(
        min(Query1[DeliveryRealTime]),
        FILTER(ALL(Query1),Query1[OrderId]= _id))
return 
if(min(Query1[DeliveryRealTime]) = _control, [TotaalAmount], BLANK())

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Hi, 

 

Yes, this works!

 

Thank you very much!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.