Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Need Assistance

Hello All - I need your help with DAX to get the route type of each InvoiceID.

 

I have attached the sample data for your reference. Click Here

 

Condition

Of each InvoiceDetailID number – If Table XO “O” value is 2  or more times

 

AND

Of each InvoiceDetailID number –

If arrcitycode of first segmentID match – depcitycode of routenumber2

If arrcitycode of second segmentID match – depcitycode of routenumber3

*(Search depending on the number of all SegmentID available for each InvoiceDetailID- Max should be 10)

 

AND

Of each InvoiceDetailID number –

depcitycode of the first segmentID match – depcitycode of last segmentID

 

Return RETURN

 

ELSE  MULTI

 

 

Thanks

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  gauravnarchal ,

    Here are the steps you can follow：

    1. Enter Power Query through Transform data, Add column -From 1.

    2. Create calculated column.

    Column = RANKX(FILTER(ALL('Table'),'Table'[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])),'Table'[Index],,ASC)
    Column 2 =
var _index= MAXX(FILTER('Table','Table'[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD]) && 'Table'[Column]<EARLIER('Table'[Column])),[Column])
var _last=MAXX(FILTER('Table','Table'[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])),[Column])
var _all=IF(_index=BLANK(),_last,_index)
return LOOKUPVALUE('Table'[Arrcity],'Table'[InvoiceDetailD],[InvoiceDetailD],[Column],_all)
    Column 3 = IF([Column 2]=[DepCity],1,0)
    Column 4 = SUMX(FILTER('Table',[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])),[Column 3])
    Column 5 = COUNTROWS(FILTER('Table',[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])))
    Column 6 = IF([Column 4]=[Column 5],1)

    3. Create calculated table.

    Table 2 =
var _1= FILTER('Table',[Column 6]=1&&'Table'[Column 5]=4)
return
SELECTCOLUMNS(_1,"RouteID",[RouteID],"InvoiceID",[InvoiceID],"InvoiceDetailD",'Table'[InvoiceDetailD],"RouteNumber",[RouteNumber],"DepCity",[DepCity],"Arrcity",[Arrcity],"XO",[XO])

    4. Result:

     

    Best Regards,

    Liu Yang

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  gauravnarchal ,

    I cannot access your data.

     

    Best Regards,

    Liu Yang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  gauravnarchal ,

    Here are the steps you can follow：

    1. Enter Power Query through Transform data, Add column -From 1.

    2. Create calculated column.

    Column = RANKX(FILTER(ALL('Table'),'Table'[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])),'Table'[Index],,ASC)
    Column 2 =
var _index= MAXX(FILTER('Table','Table'[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD]) && 'Table'[Column]<EARLIER('Table'[Column])),[Column])
var _last=MAXX(FILTER('Table','Table'[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])),[Column])
var _all=IF(_index=BLANK(),_last,_index)
return LOOKUPVALUE('Table'[Arrcity],'Table'[InvoiceDetailD],[InvoiceDetailD],[Column],_all)
    Column 3 = IF([Column 2]=[DepCity],1,0)
    Column 4 = SUMX(FILTER('Table',[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])),[Column 3])
    Column 5 = COUNTROWS(FILTER('Table',[InvoiceDetailD]=EARLIER('Table'[InvoiceDetailD])))
    Column 6 = IF([Column 4]=[Column 5],1)

    3. Create calculated table.

    Table 2 =
var _1= FILTER('Table',[Column 6]=1&&'Table'[Column 5]=4)
return
SELECTCOLUMNS(_1,"RouteID",[RouteID],"InvoiceID",[InvoiceID],"InvoiceDetailD",'Table'[InvoiceDetailD],"RouteNumber",[RouteNumber],"DepCity",[DepCity],"Arrcity",[Arrcity],"XO",[XO])

    4. Result:

     

    Best Regards,

    Liu Yang

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