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

[ADDHOPE]

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

order Data

repair 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: 

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,

[v-yueyunzh-msft · Accepted solution]

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:

(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:

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

[ADDHOPE]

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.

I am curious about it.

Regards

[v-yueyunzh-msft]

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

[ADDHOPE]

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]

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

[ADDHOPE]

Hi Shaurya,

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

Can you help me with this.

Thank you.