Forum Discussion

Stymied9's avatar
Stymied9
Frequent Visitor
9 years ago

DAX function to M language?

I have the following DAX function which works in Power BI.  

 

New Formula Output = IF('Global DSR MTMS'[TOTAL WEIGHT]>(19958 * 0.85), "Optimized", SWITCH('Global DSR MTMS'[EQUIPMENT TYPE], "20FT_COFC", IF('Global DSR MTMS'[CBM]>(33 * 0.85), "Optimized", "Not Optimized"), "40FT_COFC", IF('Global DSR MTMS'[CBM]>(67 * 0.85), "Optimized", "Not Optimized"), "40FT_HC", IF('Global DSR MTMS'[CBM]> (76 * 0.85), "Optimized", "Not Optimized"), "Not Optimized" ) )

 

Unfortunately, I need to do some groupings with this and other columns.  The problem I have is all of my columns were built outside of the query editor, so I do not have access to them in the grouping option.  So I had to rebuild them all in the query editor which is M language...and this is the last funtion for me to complete, and I cannot seem to make it work. 

Any help would be greatly appreciated.

 

Thanks !

Need Help

3 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago
    Stymied9 wrote:

    I have the following DAX function which works in Power BI.  

     

    New Formula Output = IF('Global DSR MTMS'[TOTAL WEIGHT]>(19958 * 0.85), "Optimized", SWITCH('Global DSR MTMS'[EQUIPMENT TYPE], "20FT_COFC", IF('Global DSR MTMS'[CBM]>(33 * 0.85), "Optimized", "Not Optimized"), "40FT_COFC", IF('Global DSR MTMS'[CBM]>(67 * 0.85), "Optimized", "Not Optimized"), "40FT_HC", IF('Global DSR MTMS'[CBM]> (76 * 0.85), "Optimized", "Not Optimized"), "Not Optimized" ) )

     

    Unfortunately, I need to do some groupings with this and other columns.  The problem I have is all of my columns were built outside of the query editor, so I do not have access to them in the grouping option.  So I had to rebuild them all in the query editor which is M language...and this is the last funtion for me to complete, and I cannot seem to make it work. 

    Any help would be greatly appreciated.

     

    Thanks !

    Stymied9

    Could you post any sample data and expected output so that we can offer further suggestion?

    • Stymied9's avatar
      Stymied9
      Frequent Visitor
      9 years ago

      Eric,

      A colleague of mine and I have been working on this.  We have come up with a formula that is forcing no errors in the Query Editor section, however, it is not working properly.  It seems to not not be following the search requirements properly.  Instead of looking for the proper Equipment Type, and if found, going through the next steps and providing the proper response, it is providing a response for all rows. 

       

      Here is the formula we have come up with.

      if [TOTAL WEIGHT] > (19958 * 0.85) then "Optimized" else if [EQUIPMENT TYPE] = "20FT_COFC" and [CBM] > (33 * 0.85) then "Optimized" else if [EQUIPMENT TYPE] = "40FT_COFC" and [CBM] > (67 * 0.85) then "Optimized" else if [EQUIPMENT TYPE] = "40FT_HC" and [CBM] > (76 * 0.85) then "Optimized" else "Not Optimized")

      And here is some sample data:

       

       

      Please see the below graph.  For all those items highlighted in green...those are items that should make the formula provide an "Optimized" response.  If there is no green in the row, it should provide an "Not Optimized" response.  AND...if the Equipment Type does not match at all (20FT_COFC, 40FT_COFC, or 40FT_HC) the response should be a blank or null, but preferably a blank.

       

       

      I hope that makes sense. 

       

      Thanks !