Forum Discussion

bikelley's avatar
bikelley
Icon for Helper IV rankHelper IV
5 years ago
Solved

Help with converting DAX to M in most efficient way

Hello, 


I have an M query that needs to changes, but I am not familiar with M. 


The issue is this query relying a lot on [Region]. I want to minimize the use of this column. I want to leverage the [Account.Region_Detail] column and  [Account.Region__c]. If both columns are blank then only use the [Region]. (Those 3 columns coming from 3 different tables)


Condition - If we used [Region] we have to change the value "META" = "iMETA" and "EMEA" = "EUR". 


For the DAX query I came up with,  please help me to add the condition as well. (my dax query is missing the condition, not sure how to add it in the best way) 

 

Region_New = IF(df_ProjectSummary[Account.Sub_Region__c] = "iMETA", "iMETA",
                IF(df_ProjectSummary[Account.Region__c] = "EMEA", "EUR",
                    IF(df_ProjectSummary[Account.Region__c] = "" && df_ProjectSummary[Account.Sub_Region__c] ="", df_ProjectSummary[Region],
                        df_ProjectSummary[Account.Region__c])))

 


Any help to convert this to M will really help full, and also please help me to add the condition as well in a most efficient way. 

Data looks like- 

 

Note - I tried editing this but I got few errors, this is the only one I captured, "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?".


Thank you 

Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    bikelley Maybe:

    = Table.AddColumn(#"Replaced Value1", "Region Adj",
each if [Account.Sub_Region__c] = "iMETA" then "iMETA"
else if [Account.Region__c] = "EMEA" then "EUR"
else if Text.Contains([Region],"META") or Text.Contains([Region],"EMEA") then "EUR"
else if [Account.Region__c] = "" and [Account.Sub_Region__c] ="" then [Region]
else [Account.Region__c])

7 Replies

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Hi,
    for this kind of thing, it is better to do it in Power Query, why do you need to do it in DAX?

    What's the problem with your Power Query approach?

    • bikelley's avatar
      bikelley
      Icon for Helper IV rankHelper IV
      5 years ago

      I am not familiar with Power Query. I was running into so many errors. That is the issue.  Thank you so much 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    bikelley Seems like maybe:

    = Table.AddColumn(#"Replaced Value1", "Region Adj",
each if [Account.Sub_Region__c] = "iMETA" then "iMETA"
else if [Account.Region__c] = "EMEA" then "EUR"
else if [Account.Region__c] = "" && [Account.Sub_Region__c] ="" then [Region]
else [Account.Region__c])
    • bikelley's avatar
      bikelley
      Icon for Helper IV rankHelper IV
      5 years ago

      Greg_Deckler 

      Thank you so much for taking the time to look at my issue. I think your Power Query is good if I can add the condition. The issue that I am stuck in I also have to include the below condition when we using [Region] in the above Power Query. Because [Region] column has "META" and "EMEA", I need to change the name as below. 

       

      Can you please help me to add the below condition to Power Query (the best and efficient way)? 

      Contains([Region],"META") then "EUR"
Contains([Region],"EMEA") then "EUR"

       Again, thank you so much for your help and time. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        bikelley Maybe:

        = Table.AddColumn(#"Replaced Value1", "Region Adj",
each if [Account.Sub_Region__c] = "iMETA" then "iMETA"
else if [Account.Region__c] = "EMEA" then "EUR"
else if Text.Contains([Region],"META") or Text.Contains([Region],"EMEA") then "EUR"
else if [Account.Region__c] = "" and [Account.Sub_Region__c] ="" then [Region]
else [Account.Region__c])