Dax Help

[Unknown_1]

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 · Accepted solution]

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]

Hey,

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

Please help me with that.

Thank you!

[Unknown_1]

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.

[123abc]

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.