Forum Discussion

craigmon's avatar
craigmon
Icon for Helper I rankHelper I
5 years ago
Solved

Excel Maxifs to Dax

Hi Guys,

 

Is there a way to recreate the below in DAX?

 

Thanks

 

=MAXIFS(Lookup!$D$2:$D$500,Lookup!$E$2:$E$500,C2,Lookup!$A$2:$A$500,D2,Lookup!$B$2:$B$500,E2,Lookup!$C$2:$C$500,"<="&F2)

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi craigmon 

     

    Use below code to create a new column in the Result table.

    Point = 
MAXX (
    FILTER (
        'Lookup Table',
        'Lookup Table'[Department] = 'Result Table'[Department]
            && 'Lookup Table'[Section] = 'Result Table'[Section]
            && 'Lookup Table'[Question] = 'Result Table'[Question]
            && 'Lookup Table'[Score] <= 'Result Table'[Score]
    ),
    'Lookup Table'[Points]
)

     

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    craigmon ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • craigmon's avatar
      craigmon
      Icon for Helper I rankHelper I
      5 years ago

      Thanks for coming back to me

       

      The Maxif Formula below is in the Point Column which looks up the lookup table below

      =MAXIFS($D$14:$D$501,$E$14:$E$501,C4,$A$14:$A$501,D4,$B$14:$B$501,E4,$C$14:$C$501,"<="&F4)

       

      I have uploaded the Lookup table and Result table to Power BI so I'm trying to create a new column (Point) in the Result table which has the MAXIF equivalent formula.  Hope that makes sense

       

      Result Table

       

      SiteMonth_YearDepartmentSectionQuestionScorePoint
      LeedsAug21'New CarsTargetVolume70010
      LeedsAug21'New CarsLoyaltyRenewal29.122
      LeedsAug21'New CarsQualityQuality 14.95
      LeedsAug21'New CarsFunnelLead Managament Quality602
      LeedsAug21'New CarsFunnelLead Management Speed755
      LeedsAug21'New CarsDataData Completeness928
      LeedsAug21'New CarsPeopleCertification747

       

      Lookup Table

      SectionQuestionScorePointsDepartment
      TargetVolume00New Cars
      TargetVolume911New Cars
      TargetVolume922New Cars
      TargetVolume933New Cars
      TargetVolume944New Cars
      TargetVolume955New Cars
      TargetVolume966New Cars
      TargetVolume977New Cars
      TargetVolume988New Cars
      TargetVolume999New Cars
      TargetVolume10010New Cars
      LoyaltyRenewal00New Cars
      LoyaltyRenewal271New Cars
      LoyaltyRenewal292New Cars
      LoyaltyRenewal313New Cars
      LoyaltyRenewal334New Cars
      LoyaltyRenewal355New Cars
      LoyaltyRenewal376New Cars
      LoyaltyRenewal397New Cars
      LoyaltyRenewal418New Cars
      LoyaltyRenewal439New Cars
      LoyaltyRenewal4510New Cars
      QualityQuality 100New Cars
      QualityQuality 14.811New Cars
      QualityQuality 14.832New Cars
      QualityQuality 14.853New Cars
      QualityQuality 14.874New Cars
      QualityQuality 14.895New Cars
      QualityQuality 14.916New Cars
      QualityQuality 14.937New Cars
      QualityQuality 14.958New Cars
      QualityQuality 14.979New Cars
      QualityQuality 14.9910New Cars
      FunnelLead Managament Quality00New Cars
      FunnelLead Managament Quality551New Cars
      FunnelLead Managament Quality602New Cars
      FunnelLead Managament Quality653New Cars
      FunnelLead Managament Quality704New Cars
      FunnelLead Managament Quality755New Cars
      FunnelLead Managament Quality806New Cars
      FunnelLead Managament Quality857New Cars
      FunnelLead Managament Quality908New Cars
      FunnelLead Managament Quality959New Cars
      FunnelLead Managament Quality10010New Cars
      FunnelLead Management Speed00New Cars
      FunnelLead Management Speed551New Cars
      FunnelLead Management Speed602New Cars
      FunnelLead Management Speed653New Cars
      FunnelLead Management Speed704New Cars
      FunnelLead Management Speed755New Cars
      FunnelLead Management Speed806New Cars
      FunnelLead Management Speed857New Cars
      FunnelLead Management Speed908New Cars
      FunnelLead Management Speed959New Cars
      FunnelLead Management Speed10010New Cars
      DataData Completeness00New Cars
      DataData Completeness551New Cars
      DataData Completeness602New Cars
      DataData Completeness653New Cars
      DataData Completeness704New Cars
      DataData Completeness755New Cars
      DataData Completeness806New Cars
      DataData Completeness857New Cars
      DataData Completeness908New Cars
      DataData Completeness959New Cars
      DataData Completeness10010New Cars
      PeopleCertification00New Cars
      PeopleCertification501New Cars
      PeopleCertification552New Cars
      PeopleCertification603New Cars
      PeopleCertification654New Cars
      PeopleCertification705New Cars
      PeopleCertification726New Cars
      PeopleCertification747New Cars
      PeopleCertification768New Cars
      PeopleCertification789New Cars
      PeopleCertification8010New Cars
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

        This calculated column formula works

        =CALCULATE(MAX(Table4[Points]),FILTER(Table4,Table4[Department]=EARLIER(Table3[Department])&&Table4[Section]=EARLIER(Table3[Section])&&Table4[Question]=EARLIER(Table3[Question])&&Table4[Score]<=EARLIER(Table3[Score])))

        Hope this helps.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi craigmon 

     

    Use below code to create a new column in the Result table.

    Point = 
MAXX (
    FILTER (
        'Lookup Table',
        'Lookup Table'[Department] = 'Result Table'[Department]
            && 'Lookup Table'[Section] = 'Result Table'[Section]
            && 'Lookup Table'[Question] = 'Result Table'[Question]
            && 'Lookup Table'[Score] <= 'Result Table'[Score]
    ),
    'Lookup Table'[Points]
)

     

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.