Dax

[DominicHill]

Hi Folks, Please be gentle! I have been using DAX for a couple of months, and have managed quite well so far.

The problem is have is:

The powerpivot data looks like this. I have stripped out columns you don't need to see

Portcheck2	Route Builder	Ulitmate Trade Route	Trade 2	Num_of_calls	Port_call_number	Check Col
NewP		Indian Sub Cont & Middle East to Far East		8	1	5220
Ignore				8	2	5221
NewR	Indian Sub Cont & Middle East to Sub Saharan Africa		via Sub Saharan Africa	8	3	5222
ignore				8	4	5223
newr	Indian Sub Cont & Middle East to Indian Sub Cont & Middle East		via Indian Sub Cont & Middle East	8	5	5224
NewR	Indian Sub Cont & Middle East to Far East		via Far East	8	6	5225
ignore				8	7	5226
VoyEnd				8	8	5227

What I want to achieve is where Portcheck2 is equal to "VoyEnd", then I 'join' the 'Ultimate Trade Route' with the items in Trade 2. - so I end up with 'Indian Sub Cont & Middle East to Far East via Sub Saharan Africa and via Indian Sub Cont & Middle East'. 

The Check Col are unique_ID's - I have just copied them, and I can reference them with the port_call_number and number of calls.  For the life of me I can't figure out how to do this!  I know that you 'don't do loops' in BI,  but this strikes me as the only way of doing.    

In longhand I am thinking

If the PortCheck2 column is equal to "VoyEnd", then take the value of the ultimate trade route where the data started (Lookupvalue of the Ulitmate Trade route at Col Check, Col Check-[Port_call_number]+1), and then join all non blank rows of Trade 2 between the start of Check Col, and the End of check col.   The Check Col boundaries are calculated using port_call_number.

Of course, this may not be possible, in which case I need to find another way!

Hope someone can help me!

[BhaveshPatel]

Hi @DominicHill

This seems really confusing. 

The specifications does not seem to be quite clear. From my initial understanding the scenario is more applicable to Power Query than DAX logic. 

Please provide more clear explanations.

[DominicHill]

Hi,  I can try and make it clearer.

The last column (check col) contains a unique reference number, for the start and end of the data needs to be looked at.

Between these two rows  (i.e. in Check Col, there is 5220 through 5227).

Starting row with reference 5220, Ultimate Trade route contains 'Indian Sub Cont & Middle East to Far East'.

5221 is blank, so can be ignored

5222 trade 2 column has 'via Sub Saharan Africa'

5223 is blank, therefore ignored

5224 trade 2 column has 'via Indian Sub Cont & Middle East'

5225 trade 2 column has 'via Far East'

5226 is blank

5227 is blank, but PortCheck2 contains 'VoyEnd', so I need to join the contents of 'Ultimate Trade Route'' (at 5220) to the contents of colum Trade 2 (at 5222 and 5224 and 5225 and 5227), but only for the rows between 5220 and 5227.  The next set of rows will start the process again.

If the final result needs to be in a different column, that's fine.

This is being done using Dax in a powerpivot worksheet, and the results will be used as a filter in a Pivot table.

If this is more suitable to an SQL query, then I can look a that, but it will set me back!  (Not that I am making any headway at the moment).

Thanks,

Dom

[v-danhe-msft]

Hi @DominicHill ,

Could you please post your sample file to have a test and post your desired result if possible?

Regards,
Daniel He