Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ADDHOPE
Frequent Visitor

how to find the second value for the selected item in power bi dax and show it as new column value

Hello All,

 

I am struglling to find a way to calulcate the second high value from other table. 

The sample data is as below. Both table are connected on ItemID

 

ADDHOPE_3-1669045458072.pngorder Data

ADDHOPE_4-1669045496582.pngrepair data

 

Table 1 is order data and Table 2 is repair data.

My objective is to find the 2nd country of movement and afterwards if present 3rd.

Expected result: 

ADDHOPE_2-1669045196446.png

Here: Repair date decide the rank

(First repair country = Earliest RepairDate )

(Second repair country = Second Earliest RepairDate )

 

Any help will be highly appreciated.

Thank you in advance!

Regards,

 

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @ADDHOPE 

According to your dedscription, you want to " find the 2nd country of movement and afterwards if present 3rd.".

Here are the steps you can refer to :

(1)My test data is the same as yours.

(2)We can modify our 'Table2' in Power Query Editor, you can put this in the "Advanced Editor" :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJPLcpNzKsEskz0DU31jQyMDJRidSBybkWJecmpQIalvrEBQsoYKOKZl5KZCKQNDfUNTUBSRmApI5BUSWJOJViTkTlIxhDTPLguAyy6DI2gumIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ItemId = _t, RepaireCountry = _t, RepairDate = _t]),
    test = Table.TransformColumnTypes(Source,{{"ItemId", Int64.Type}, {"RepaireCountry", type text}, {"RepairDate", type date}}),
    Custom1 = Table.Group(test,"ItemId",{"test",(x)=>Table.AddIndexColumn(Table.Sort(x,"RepairDate"),"Index",1,1,Int64.Type)      }),
    #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"RepaireCountry", "RepairDate", "Index"}, {"RepaireCountry", "RepairDate", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded test",{{"Index", Int64.Type}})
in
    #"Changed Type"

Then we can get this table2:

vyueyunzhmsft_0-1669087613159.png

(3)Then we apply the data to the Power BI Desktop and we can click "New Table" to create a table:

Table = SELECTCOLUMNS( 'Table1' , "ItemID" ,[ItemId] , "CountrySold" , [CountrySold] , "First repair country" ,var _item = [ItemId] return MAXX(FILTER('Table2' , 'Table2'[Index]=1 && 'Table2'[ItemId]=_item ),[RepaireCountry]) ,"Second repair country", var _item = [ItemId] return MAXX(FILTER('Table2' , 'Table2'[Index]=2 && 'Table2'[ItemId]=_item ),[RepaireCountry]),"Third repair country",var _item = [ItemId] return MAXX(FILTER('Table2' , 'Table2'[Index]=3 && 'Table2'[ItemId]=_item ),[RepaireCountry])   )

(4)Then we can meet your need , the result is as follows:

vyueyunzhmsft_1-1669087664428.png

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

View solution in original post

6 REPLIES 6
v-yueyunzh-msft
Community Support
Community Support

Hi, @ADDHOPE 

According to your dedscription, you want to " find the 2nd country of movement and afterwards if present 3rd.".

Here are the steps you can refer to :

(1)My test data is the same as yours.

(2)We can modify our 'Table2' in Power Query Editor, you can put this in the "Advanced Editor" :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJPLcpNzKsEskz0DU31jQyMDJRidSBybkWJecmpQIalvrEBQsoYKOKZl5KZCKQNDfUNTUBSRmApI5BUSWJOJViTkTlIxhDTPLguAyy6DI2gumIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ItemId = _t, RepaireCountry = _t, RepairDate = _t]),
    test = Table.TransformColumnTypes(Source,{{"ItemId", Int64.Type}, {"RepaireCountry", type text}, {"RepairDate", type date}}),
    Custom1 = Table.Group(test,"ItemId",{"test",(x)=>Table.AddIndexColumn(Table.Sort(x,"RepairDate"),"Index",1,1,Int64.Type)      }),
    #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"RepaireCountry", "RepairDate", "Index"}, {"RepaireCountry", "RepairDate", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded test",{{"Index", Int64.Type}})
in
    #"Changed Type"

Then we can get this table2:

vyueyunzhmsft_0-1669087613159.png

(3)Then we apply the data to the Power BI Desktop and we can click "New Table" to create a table:

Table = SELECTCOLUMNS( 'Table1' , "ItemID" ,[ItemId] , "CountrySold" , [CountrySold] , "First repair country" ,var _item = [ItemId] return MAXX(FILTER('Table2' , 'Table2'[Index]=1 && 'Table2'[ItemId]=_item ),[RepaireCountry]) ,"Second repair country", var _item = [ItemId] return MAXX(FILTER('Table2' , 'Table2'[Index]=2 && 'Table2'[ItemId]=_item ),[RepaireCountry]),"Third repair country",var _item = [ItemId] return MAXX(FILTER('Table2' , 'Table2'[Index]=3 && 'Table2'[ItemId]=_item ),[RepaireCountry])   )

(4)Then we can meet your need , the result is as follows:

vyueyunzhmsft_1-1669087664428.png

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

Hi Aniya Zhang,

 

Thank you for your response, it does work on the test data. 

But can you explain this line of code so i can implement it my actual report as well.

ADDHOPE_0-1669125694400.png

 

I am curious about it.

 

Regards

Hi , @ADDHOPE 

This code means :

Group according to [ItemID], then sort from smallest to largest according to [RepairDate], and then number the table "1,2,3...", and then we can get the first three corresponding values according to [Index].

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

Hello Aniya Zhang,

 

Thank you again. 

I have one more question. Continuing to my evaluation I have to now find out the ID which have different sales country and different repair country. I can do that easily for 1st repair country by this formula.

1st Repair = CALCULATE(COUNT('Table1'[ItemID],'Table2'[RepairCountry] <> SELECTEDVALUE('Table1'[SalesCountry]))

But now I have to find get all the ID which have different repair country than sales country. i.e. I want to get those IDs as well if the 2nd or 3rd repair countries are not matching.

 

So the table should list all the ItemID where the Repair country does not match the sales country irrespective of the instance, where it happened in 3rd or 2nd.

 

Can you please help 🙂

 

Regards,

Shaurya
Memorable Member
Memorable Member

HI @ADDHOPE,

 

Create a rank for the repair date for the same ItemID and use that as filters to get the second, third repair date and so on.

 

Rank = RANKX(FILTER('Table','Table'[ItemID]=EARLIER('Table'[ItemID])),'Table'[RepairDate],,ASC,DENSE)

 

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

Hi Shaurya,

 

Thank you for quick reply. But it shows me below error.

ADDHOPE_0-1669057756067.png

Can you help me with this.

 

Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.