cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sowmiya Member
Member

DAX Formula

 

Screenshot.pngHi Community,
Help me with DAX that returns the Largest S.NO's Corresponding Description but Description must be nonblank.
In the Screenshot, DAX must return Animal. S.NO - 6 is Largest but Description is Blank so excluding the blank, S.NO 5 is large and so Animal must be the answer.




Thanks in Advance,
Sowmiya.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
themistoklis New Contributor
New Contributor

Re: DAX Formula

@Sowmiya

 

This will give you the highest value:

 

Highest Value = LOOKUPVALUE('Table'[Description],'Table'[S.NO],CALCULATE(MAX('Table'[S.NO]), 'Table'[Description] <>""))

image.pngI

 

It also works when you have multiple categories. Image below:

image.png

 

2 REPLIES 2
CR Member
Member

Re: DAX Formula

Hi @Sowmiya

 

In your Query editor, you can use that code (you just need to customize the source or just to start from the lines you need, such as Inserted Merged Columns).

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdJuRaWZJUqxOtFKRkCec345SC4vMzcxByxoDBLMSC0uBtKeeSn5+UUK7om5qWA5E6CYT35JKUjOLSe/PLUILGwK5PrnpaKaYwbkBmTkg4WVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [S.NO = _t, Name = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S.NO", Int64.Type}, {"Name", type text}, {"Description", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "ID", each Text.Combine({[Description], Text.From([S.NO], "fr-FR")}, "_"), type text),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Column",{{"ID", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 2, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Description"}, {"Added Index1.S.NO"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Added Index1", "LARGEST S.NO", each if [Added Index1.S.NO] = [Description] then "NO" else "YES")
in
    #"Added Conditional Column"

After having sort on the new ID column (concatenation of Description ad S.NO), I added 2 indexes to make a Merge Queries inside the same table (as PowerBi is working by line, not by cell, I cheat with double indexes to display the ClientID value from the next line). Then, I just need to add a calculated column saying that => IF Description from next line is the same as the current Description then display NO else YES.

 

1.png

 

Regards,

CR

Highlighted
themistoklis New Contributor
New Contributor

Re: DAX Formula

@Sowmiya

 

This will give you the highest value:

 

Highest Value = LOOKUPVALUE('Table'[Description],'Table'[S.NO],CALCULATE(MAX('Table'[S.NO]), 'Table'[Description] <>""))

image.pngI

 

It also works when you have multiple categories. Image below:

image.png

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 134 members 1,612 guests
Please welcome our newest community members: