how to get the right value

[Norbertus]

hi

how can i realise this?

situation Red Value ... for date x we have a max Version with the value 2. This Version is unique so we need the value 14000

situation Green Value... for date y we have a max Version that is double. Then we need the value with the highes Time. 10000

This needed to be calculated for each Location

Date	Time	Version	Location	Value
1-1-2022	17:00	1	A	12000
1-1-2022	15:00	2	A	14000
2-1-2022	16:00	3	B	9000
2-1-2022	17:00	4	B	10000
2-1-2022	15:00	4	B	11000

[v-luwang-msft · Accepted solution]

Hi @Norbertus ,

Test the below measure:

test = 
VAR maxversion =
    CALCULATE (
        MAX ( 'Table'[Version] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) )
    )
VAR count1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Version] = maxversion
        )
    )
VAR maxdata =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) )
    )
VAR test1 =
    IF (
        count1 = 1,
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) )
        ),
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date] = MAX ( 'Table'[Date] )
                    && 'Table'[Time] = maxdata
            )
        )
    )
RETURN
    IF (
        count1 = 1
            && MAX ( 'Table'[Value] ) = test1,
        "red",
        IF ( count1 > 1 && MAX ( 'Table'[Value] ) = test1, "green", BLANK () )
    )

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Norbertus]

Hi @v-luwang-msft ,

you make my day!! 

After a small changes for my solution it works great. 

[amitchandak]

@Norbertus , A measure like

sumx(values(Table[Location]), calculate(max(Table[Values]), allexcept(Table, Table[Location])) )

[Norbertus]

Hi @amitchandak 

Thanks, but...
This solution doesn't take account of the second rule. When there are more rows of the same version by day give then the value with the higest time