Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
6 years ago
Solved

Convert DAX to M

Hi All,   I am trying to move all my calculated colums to the query editor which requires to translate DAX statement to M. I am not sure it is always possible. Here is an example where I can't make...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Arnault_ ,

    first I had to convert the values from the column TS05_packing_date into something that my Power Query was able to recognize as a date, this means my table looks like this:

    Here is my solution that is based on two steps:

    1. Determinig the min date per order_id

    For this I grouped the table by order_id, but also added the aggregation function "All Rows"

    After expanding the group my table looks like this:

    2. Adding a custom column to check the date

    I added a custom column "checkFirstDate" using this M expression:

    if [TS05_packing_date] <= Date.AddDays([MinDate], 😎 then "yes" else "no"

    Finally the table looks like this, of course the the column "MinDate" can be deleted from the table as an additional step:

     

    Hopefully this provides you with some ideas, how to tackle your requirement.

     

    Regards,

    Tom

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey Arnault_ ,

first I had to convert the values from the column TS05_packing_date into something that my Power Query was able to recognize as a date, this means my table looks like this:

Here is my solution that is based on two steps:

1. Determinig the min date per order_id

For this I grouped the table by order_id, but also added the aggregation function "All Rows"

After expanding the group my table looks like this:

2. Adding a custom column to check the date

I added a custom column "checkFirstDate" using this M expression:

if [TS05_packing_date] <= Date.AddDays([MinDate], 😎 then "yes" else "no"

Finally the table looks like this, of course the the column "MinDate" can be deleted from the table as an additional step:

 

Hopefully this provides you with some ideas, how to tackle your requirement.

 

Regards,

Tom

v_ichharam's avatar
v_ichharam
Frequent Visitor
3 years ago

Hello. Can anyone help me convert the following dax code to M query? I have a dataset where "value" is cumulative for a year i.e. YTD. I need monthly value so that I can append this dataset to another table with the same variables where value is "monthly" and NOT YTD. Thank you

Month_value_calc =
VAR PREVValue =
CALCULATE(
MAX(Consol_All_Yrs_Segment_Sector_Country[YTD_ZAR_Value]),
Consol_All_Yrs_Segment_Sector_Country[Country] = EARLIER(Consol_All_Yrs_Segment_Sector_Country[Country]),
Consol_All_Yrs_Segment_Sector_Country[Financial metric] = EARLIER(Consol_All_Yrs_Segment_Sector_Country[Financial metric]),
Consol_All_Yrs_Segment_Sector_Country[Segment_sector] = EARLIER(Consol_All_Yrs_Segment_Sector_Country[Segment_sector]),
Consol_All_Yrs_Segment_Sector_Country[Year] = EARLIER(Consol_All_Yrs_Segment_Sector_Country[Year]),
Consol_All_Yrs_Segment_Sector_Country[Month] = EARLIER(Consol_All_Yrs_Segment_Sector_Country[Month])-1,
REMOVEFILTERS(Consol_All_Yrs_Segment_Sector_Country)
)
RETURN
IF(MONTH(Consol_All_Yrs_Segment_Sector_Country[Period]) = 1, Consol_All_Yrs_Segment_Sector_Country[YTD_ZAR_Value],
Consol_All_Yrs_Segment_Sector_Country[YTD_ZAR_Value] - PREVValue)