Incorporate Status Based On Max Values in Virtual Table

DAX DAX Calculations
1

Hi,

I’d like to have the ‘Status’ from the Trial Table pulled into the Virtual Trial Table.

The Virtual Table code is as follows;

Virtual Trial Table = 
SUMMARIZE (
    'Trial Table',
    'Trial Table'[Vendor Name],
    'Trial Table'[SRO #],
    "Value", CALCULATE ( MAX ( 'Trial Table'[Value] ) ),
    "Last Modified", CALCULATE ( MAX ( 'Trial Table'[Modified] ) )
)

How can I get the ‘Status’ into the V table where and still keep the SRO, Vendor name, Value and Last modified unique? Possible?

Trial.xlsx (10.5 KB)
Trials.pbix (34.4 KB)

2

@cms418

Virtual Trial Table = 
SELECTCOLUMNS (
    'Trial Table',
    "Vendor Name", 'Trial Table'[Vendor Name],
    "SRO #", 'Trial Table'[SRO #],
    "Value", CALCULATE ( MAX ( 'Trial Table'[Value] ) ),
    "Last Modified", CALCULATE ( MAX ( 'Trial Table'[Modified] ) )
)
3

Thanks but this leaves the Henkels and Strike as not unique , how can I keep it unique and use the status from the Max value and Last modified?

image
image1065×548 34.5 KB

4

@cms418 Not sure, what you are trying to do, share a table that shows the end result.

5

Hi @AntrikshSharma - 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.

Excel table End result attached and below.

image
image771×245 15.1 KB

End Result.xlsx (10.0 KB)
Trial.xlsx (10.5 KB)
Trials.pbix (36.5 KB)

6

Solved this on my own.

Created a Key b/w the 2 tables and then used lookupvalue to bring in the status.

image
image777×471 10.9 KB

Trials.pbix (38.5 KB)
Trial.xlsx (10.5 KB)