Most efficient way to create reference tables from original table

[Anonymous]

Hi there,

I have a primary table in my PowerBI model, and want to perform several transformations to add custom columns.  

For example, my primary table contains a unique Customer ID per row, with a column for Parent ID.  For some rows, these two values will be the same, i.e. it's a Parent record, but for others they will differ, i.e. they are a child record.  Every individual row has a Last Booking Date.

I want to add a new column to my primary table which shows the most recent Last Booking Date for each differing Parent ID, so I have created a duplicate of my primary table, grouping on Parent ID and the MAX of Last Booking Date.

I've then merged this second table to the primary table, joining on Parent ID, and then expanded the columns for MAX Last Booking Date.

This achieves my goals, and I get the data I require in the new column, however I'm concerned that it might not be the most efficient mechanism to do so.

I've a number of similar scenarios I've run against my primary table, ending up with 3 duplicate tables - against which I've performed various types of data transformation.

It now takes quite a long time to refresh table previews, and I'm wondering if it's because each of the tables need to refresh in full to update my primary table.

Is there a more efficient way of achieving this type of manipulation, e.g. instead of duplicating the primary table, should I be referencing it?

Hope this makes sense anyway!

Thanks

[Anonymous]

Hi @v-janeyg-msft ,

Sorry, I'm not sure I understand.  The column you've highlghted in this sample table is the expected result.

Both rows have the same Parent Cust ID, but differing Cust Last Booked Date.  The Parent Last Booking Date is displaying the MAX Cust Last Booking Date across all child entities.

Naturally, this value will be repeated or all rows with the same Parent Cust ID, but this is not a problem.

Hope that clarifies?

C

[v-janeyg-msft]

@Anonymous  So you have no questions, do you?

[Anonymous]

Erm, not sure I understand.

I do have a question...as described...

"Ok, so next problem (sorry!).  I actually need to expose the MAX(Last Booking Date) across all Customer entities associated with the Parent Customer.  If I create a measure using Last Booking Date = MAX ( Table[Booking Date] ) then I get the Last Booking Date for the individual Customer in my report - which is at Customer Level."

That table was jsut an example of the Parent Cust Last Booking Date column I'm hoping to create.

[v-janeyg-msft]

@Anonymous  

If you only want to display the max date.

You can try:

Last Booking Date =
MAXX ( ALL ( table ), [Booking Date] )

Best Regards,

Community Support Team _Janey

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

[Anonymous]

Nope, this doesn't work, soz.  It just returns the most recent Cust Last Booking Date from the entire table, and duplicates it down the column. 😞

I need the most recent Cust Last Booking Date for rows where the Parent Cust ID is the same, as per my example.

Sorry, I'm not sure how else to explain it.  Perhaps this extended example helps?

Cust ID	Cust Last Booking Date	Parent Cust ID	Parent Last Booking Date
1234	01/12/20	9999	15/03/21
4567	15/03/21	9999	15/03/21
9876	05/09/21	4789	05/09/21
5679	01/10/19	4789	05/09/21
2345	06/07/21	1111	06/07/21
4613	05/09/20	1111	06/07/21

I'm aiming to achieve the blue column data.

[v-janeyg-msft · Accepted solution]

Hi @Anonymous 

That's why I asked you to share sample data...Your previous sample is incomplete.

Try this:

Last Booking Date =
MAXX (
    FILTER (
        ALL ( table ),
        [Parent Cust ID] = SELECTEDVALUE ( table[Parent Cust ID] )
    ),
    [Booking Date]
)

Best Regards,

Community Support Team _Janey

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

[parry2k]

@Anonymous Two ways to handle it:

- if you want to use PQ, keep all transformations in one table and use it as a reference.

- or best way is to use DAX measure, no additional tables will be required, just use one table with all the transformation and thru measure achieve the result. In the above example, just add a below measure and in table visual, put parent id and the measure, it should work

Last Booking Date = MAX ( Table[Booking Date] )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

Perfect, thank you.  😁  DAX is a much better way of course! 🙄

Ok, so next problem (sorry!).  I actually need to expose the MAX(Last Booking Date) across all Customer entities associated with the Parent Customer.  If I create a measure using Last Booking Date = MAX ( Table[Booking Date] ) then I get the Last Booking Date for the individual Customer in my report - which is at Customer Level.

This is what I'm after, i.e. the Parent Last Booking date is the MAX of all Booking Dates associated with child Customer entities.

Cust ID	Cust Last Booking Date	Parent Cust ID	Parent Last Booking Date
1234	01/12/20	9999	15/03/21
4567	15/03/21	9999	15/03/21

Thanks! 

[v-janeyg-msft]

Hi, @Anonymous 

So what should be the correct result?

Can you share some sample data? So we can modify the code according to the field.

Janey