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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Kishorb
Frequent Visitor

DAX

HI , I  need a help  in correcting measure defination which was display 'second lowest value' into a table but when i used this measure into Card visual then blank() value displayed. 

I have created below measure which is calculated Deptname and first lowest and second lowest values into table visuals based on SalesRep Slicer values selection

 

1. SalesDeptName = if(isfiltered('Sales Data'[SalesRep]),selectedvalue('Sales Data'[Dep]))

2. First Lowest Target = Calculate(Calculate(Min('Sales Data'[Target]),AllExcept('Sales Data','Sales Data'[Dep])),filter('Sales Data','Sales Data'[Dep] =[SalesDeptName]))

3. MRank2 = RANKX(filter(all('Sales Data'),'Sales Data'[Dep] = selectedvalue('Sales Data'[Dep])),Calculate(Sum('Sales Data'[Target])),,ASC,dense) = 2

4.Second lowest Quota = if( Calc[MRank2] = true(),Calculate(Sum('Sales Data'[Target])))

 

Please advice me on this

Thanks

 

Can you pls advice me on this

1 ACCEPTED SOLUTION

@Kishorb,

 

Use calculate column instead of measure to achieve the rank:

MRank2 = 
RANKX (
    FILTER (
        'Sales Data', 'Sales Data'[Dep] = EARLIER('Sales Data'[Dep])
    ),
    RANKX ( ALL ( 'Sales Data' ), 'Sales Data'[Target],, DESC, Dense)
)

Then modify the measure like below:

Second lowest Quota = 
VAR lowest_count = COUNTROWS(FILTER('Sales Data', 'Sales Data'[MRank2] = 1))
RETURN
CALCULATE(MAX('Sales Data'[Target]), FILTER(ALL('Sales Data'), 'Sales Data'[MRank2] = lowest_count + 1))

Capture.PNG  

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Can you share example data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I have shared below sample data table for reference

 

SalesManagerSalesRepDepTarget
PeterAndrewProp1210000
PeterMacmanProp1280000
PeterAlexProp1210000
PeterJackProp178000
PeterJoeProp1120000
PeterpeterProp1120000
PeterAdamProp1120000
PeterStephanieProp1210000
PeterKirstenProp1350000
LendaAjayPro25000000
LendaRockyPro23000000
LendaPratikPro23500000
LendaHarshalPro21000000
LendaDasPro22500000
LendaVivekPro21700000
LendaLinaPro21500000
LendaDhoniPro21000000

@Kishorb,

 

Use calculate column instead of measure to achieve the rank:

MRank2 = 
RANKX (
    FILTER (
        'Sales Data', 'Sales Data'[Dep] = EARLIER('Sales Data'[Dep])
    ),
    RANKX ( ALL ( 'Sales Data' ), 'Sales Data'[Target],, DESC, Dense)
)

Then modify the measure like below:

Second lowest Quota = 
VAR lowest_count = COUNTROWS(FILTER('Sales Data', 'Sales Data'[MRank2] = 1))
RETURN
CALCULATE(MAX('Sales Data'[Target]), FILTER(ALL('Sales Data'), 'Sales Data'[MRank2] = lowest_count + 1))

Capture.PNG  

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks it's working for me

@Kishorb

 

Great to hear that. Could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.