Establish One to Many Based off Max Value in Column

[cms418]

Hi all,

In this case I am trying to create a simple table where I am able to establish a one to many relationship but with the MAX value for the Vendor and SRO combo.

As you can see in the data provided there are multiple values for the same SRO and Vendor name. What I need is a way to clean it up in either the query or via virtual table in DAX so that it can connect in my model.

I’m able to do it in a simple measure as you’ll see in the PBIX but can’t seem to get it in query or via add columns/summarize to establish the standalone table

I think this is easy but also think I’m tired from a looong year.

Thanks

CS

Trials.pbix (25.6 KB)
Trial.xlsx (10.2 KB)

[hafizsultan]

Hi @cms418 ,

Thanks for posting this question and providing sample file. I think you want to create a summary table with unique Vendor and SRO combination. Below code should be fine for that:

Summary table =
SUMMARIZE (
    'Table1',
    'Table1'[SRO #],
    Table1[Vendor Name],
    "Value", CALCULATE ( MAX ( Table1[Value] ) )
)

Kind Regards,
Hafiz

[cms418]

Thank you very much.