Outputing multiple columns from RELATEDTABLE via BI Publisher/Excel and evaluate

[masplin]

i have a database of customers and vehicles.  A customer can have multiple vehicles, but not the other way round. I have relationship between vehicles and customers.

Currently I output in excel using a BI Publisher connection using an evaluate summarise function so every vehicle is on a row

evaluate       calculatetable(    summarize(    vehicles,  Vehicles[Vehicle No],Vehicles[VRM],Vehicles[Make],Vehicles[Model],Vehicles[Last Visit Date],Vehicles[Next Service Date],Vehicles[Last Service Date],Vehicles[Last Service GP],Vehicles[Service due],Vehicles[MOT Due Date],Vehicles[Last MOT Date],Vehicles[Last MOT GP],Vehicles[Last Combi Date],Vehicles[Last Combi GP],Customer[Customer Active],Vehicles[MOT Booked],Vehicles[service Booked],Customer[Customer Group],Customer[Last Centre],Customer[E-Mail],Customer[Mobile Phone No],Customer[Address],Customer[Address 2],Customer[City],Customer[Post Code],Customer[Customer No],Customer[Salutation],Customer[First Name],Customer[Surname],Customer[Last Date Visited],Customer[Plat MOT Avail],Customer[Plat XS Avail], Customer[Plat Full Avail], Customer[Plat Int Avail],Customer[MOT Reminder Platinum],Customer[XS Reminder Platinum],"Customer LTV",[CustomerLTV]  ),          FILTER(  vehicles,  Vehicles[CRM OUTput]="yes" )    )    ORDER BY vehicles[MOT Due Date], vehicles[VRM]

This works great as just list out the fields form the Customer table to append to each vehicle. however I now have been asked to out the data the other way round with a single row for each customer and columns for the vehicles. I have created a ranking for each customers vehicles so can identify VRM1, VRM2 etc.

The idiot way of doing this would be to add columns to the customer table looking up the data for each vehicle based on rank. each vehicle has about 10 bits of data associated so if a customer has 4 vehicles that 40 additional columns.  I'm sure that will have a horrific performance impact.

The question whether there is any syntax to use in the evaluate expression that would be able to append the vehicle data to each customer line directly in excel without adding extra columns to the customer table?  I tried adding something like this in the summarize list , but doesn't work as needs a column header. 

CALCULATE(VALUES(Vehicles[VRM]),FILTER(RELATEDTABLE(Vehicles),Vehicles[Vehicle Visit Rank]=1))

any advice appreciated

Mike

[v-yuezhe-msft]

@masplin,

Please share sample data and post expected result following the guide in this thread: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

Regards,
Lydia

[masplin]

Hi Lydia

sorry didnt think of that. I've made a tiny version of it to explain

https://www.dropbox.com/sh/pa74gcb0e6ptxwh/AABhk1e1ZWuMMuUltCLkZkXya?dl=0

Essentailly 2 table one for vehicle and one ofr cusotmers linked by the customer no.   I've imported excel data to pbix then published the pbix out.  Normally I use an evalaute statement like on the "current tab" J:K, which outputs rows of vehicles with associated customer data, but for some wierd reason this workbook doesn't work (get an error if add any columns off customer table) so instead I've used a pivot table.

What I need to output is on the Required tab which is a row for each customer and then blocks of vehicle data as columns. The key thnig is a single row per cusotmer and up to 4 vehicles 

Ideally I want to write a DAX query like the evalaurte function that can pul lthe data together off the extising tables without having add numerous columns to the customer table.  Has to be output in excel not in pbix as has to be synronised to 3rd party software.

Apprecaite any ideas

Mike