Forum Discussion

Unknown_1's avatar
Unknown_1
Frequent Visitor
2 years ago
Solved

Dax Help

Hi Team,

 

I want a dax syntax in which column A is sorted Alphabetically.

Column C and Column D are checked or looked up in column A and if that match is found then in the result earlier alphabetical value is taken first followed by the next.

 

I have written the formula in Excel.

Please see the attached picture for reference.

 

Thank you!

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Unknown_1 ,

     

    Thanks for the reply from 123abc , please allow me to provide another insight: 

     

    Creating Calculated Columns, Writing Expressions

    Result = 
IF (
    MIN ( [LE], [COUNTER LE] ) & MAX ( [LE], [COUNTER LE] ) = [LE] & [COUNTER LE],
    [LE] & [COUNTER LE],
    MIN ( [LE], [COUNTER LE] ) & MAX ( [LE], [COUNTER LE] )
)

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    To sort a column alphabetically in DAX, you would typically use the ORDER BY function within a SUMMARIZE or SELECTCOLUMNS function. Here's a simple example:

     

    SortedTable =
    SORT(
    TableName,
    TableName[ColumnA], ASC
    )

     

    This code will sort the table "TableName" based on the values in "ColumnA" in ascending order.

    For the lookup operation you described, you can use DAX functions like RELATED or LOOKUPVALUE. Here's a basic example:

     

    LookupResult =
    SELECTCOLUMNS(
    TableName,
    "ColumnA", TableName[ColumnA],
    "MatchedColumnC", LOOKUPVALUE(TableName[ColumnC], TableName[ColumnA], TableName[ColumnA]),
    "MatchedColumnD", LOOKUPVALUE(TableName[ColumnD], TableName[ColumnA], TableName[ColumnA])
    )

     

    This code will create a new table with columns "ColumnA", "MatchedColumnC", and "MatchedColumnD", where "MatchedColumnC" and "MatchedColumnD" are looked up based on the value in "ColumnA".

    You can adjust these examples to suit your specific needs and integrate them into your DAX formula. If you provide more details about your specific requirements, I can tailor the code accordingly.

  • Unknown_1's avatar
    Unknown_1
    Frequent Visitor
    2 years ago

    My specific Requirement is :

    I want distinct values from Column Counter BU and Legal Entity and store those values alphabetically in a new column or table.

    Post that I want to check Counter BU and Legal Entity in the newly created table/Column and return the Alphabetically earlier value taken first.

     I hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Unknown_1 ,

     

    Thanks for the reply from 123abc , please allow me to provide another insight: 

     

    Creating Calculated Columns, Writing Expressions

    Result = 
IF (
    MIN ( [LE], [COUNTER LE] ) & MAX ( [LE], [COUNTER LE] ) = [LE] & [COUNTER LE],
    [LE] & [COUNTER LE],
    MIN ( [LE], [COUNTER LE] ) & MAX ( [LE], [COUNTER LE] )
)

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Unknown_1's avatar
      Unknown_1
      Frequent Visitor
      2 years ago

      Hey,

      How to solve the same problem when my COUNTER LE is in a different table.

      Please help me with that.

      Thank you!