Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Most efficient way to create reference tables from original table

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,...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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 IDCust Last Booking DateParent Cust IDParent Last Booking Date
    123401/12/20999915/03/21
    456715/03/21999915/03/21
    987605/09/21

    4789

    		05/09/21
    567901/10/19478905/09/21
    234506/07/21

    1111

    		06/07/21
    461305/09/20111106/07/21

     

    I'm aiming to achieve the blue column data.

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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.