large dataset with DAX table and m-n relations: how to handle nested tables?

[cdebackere]

I have a dataset with 'reservations' and with objects of various type, let's say queries 'objectsType1', etc.

The reservations are linked to several other 'resource' table records. In the reservations query, this is stored in nested table which holds the id's and types of all linked resources. There is a n-m relationship between reservations and the various 'resources' tables/queries. So in line with model best practices I modelled this with an intermediate query 'objectReservations': this is the reservations query, but expanded on the table-column, and with everything removed except the 2 columns: reservation id and object id.

So the objectReservations sits between the resevation and the objetcs, to better handle the n-m

So far so good, works like a charm on a dataset of 6months worth.

However the  'reservations' span over many years. The query to get data from 1 year takes about 15_20 minutes to load.

After earlier forum support this resulted

- in a reservations query per year, of which only the actual year is set to be 'updated' with the report. (since historic data doesn't change)

- in a dax calculated table 'allReservations' which makes the union of those reservation query.

So far so good in migrating the 'reservations' query to a 'allReservations' table.

But now I need to migrate the concept of the 'objectReservations' query to an 'allObjectReservations' table.

Here I get stuck: when these reservations were still queries, I could use expand column on the nested tables in power M 'et voila', done.

But now, as the allReservations is now a DAX table instead of a query, the 'table-column' just read 'Table'.. but I get the impression the actual nested tables are not there anymore.

So I have 3 questions:

- these nested tables in a query, when loaded into a dax calculated table: is this data stll there or lost (ie: becomes a 'Table' string)?

- if still there: how do I expand it or retrieve info from hese nested tables?

- if no longer present: how should I solve this? I tried to convert the nested table into a string with delimiter(OK), but get stuck on the DAX to convert that string into expanded rows.

 Thanks,

Christof

[johnt75 · Accepted solution]

Instead of doing the union in DAX, append the queries in Power query. Do all the transformations you need on the individual year queries, or create a function if all the transformations are the same. Untick "Enable Load" on each query, so that they are not loaded into the model as individual tables, and then merge them all into a new query.

[johnt75 · Accepted solution]

If you have to keep them as independent queries then you will need to do all the expansion and transformations on each one individually in Power Query, as you cannot access data that isn't expanded in DAX. As you say, it is just the string 'Table'.

[cdebackere]

I tried that initially, but even when not loading them into the report, all 'year queries' were refreshed when the 'allReservationInPowerQuery' was refreshed. It was suggested to me in the forum that power queries which are linked ALWAYS refresh depending on relationship, independent from the 'enable load' tick/untick. And that you had to pass over the DAX table to break thta 'dynamic' link. I can confirm that the DAX -union solution indeed result in only the current period being updated.

[johnt75 · Accepted solution]

Instead of doing the union in DAX, append the queries in Power query. Do all the transformations you need on the individual year queries, or create a function if all the transformations are the same. Untick "Enable Load" on each query, so that they are not loaded into the model as individual tables, and then merge them all into a new query.

[cdebackere]

ok. I have a generic function which takes 2 dates and does all the SOAP retrieval and transformations. The individual tables are just a call to that function with a start and end date

Thx for your help