Forum Discussion

Kishorb's avatar
Kishorb
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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))

      

     

    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.

Kishorb's avatar
Kishorb
Frequent Visitor
7 years ago

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
  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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))

      

     

    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.

    • Kishorb's avatar
      Kishorb
      Frequent Visitor
      7 years ago

      Thanks it's working for me

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        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