DAX way to find a value in a column adjacent to another value

[Anonymous]

Hi guys, newbie here!

I have a data structured like the follow

Customer	Type	Value	Flag
A	3	3.5	0
A	3	2.2	1
A	2	7.3	1
A	2	4.9	0

I need to create a Matrix that shows, for every Type, the MIN(Value) and the corresponding Flag.

In this case I want this result:

Customer	Type	MIN(val)	Flag
A	3	2.2	0
	2	4.9	0

I tried woth the default setting (min of Flag) in the Visualizations pane, but I've noticed this new case in which tha flag 0 doesn't correspond with the minimum value for this Type. 

So basically I need something like a LOOKUP to retrieve the value (for the flag column) of the cell in the same row but for the value column.

thank you a lot!

[Anonymous · Accepted solution]

Hi @Anonymous , 

Please refer to my pbix file to see if it helps you.

Create a measure.

_minvalue =
VAR _minvalue =
    CALCULATE (
        MIN ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer] = SELECTEDVALUE ( 'Table'[Customer] )
                && 'Table'[Type] = SELECTEDVALUE ( 'Table'[Type] )
        )
    )
RETURN
    IF (
        MAX ( 'Table'[Value] ) = _minvalue,
        MINX ( ALL ( 'Table' ), 'Table'[Flag] ),
        BLANK ()
    )

f I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@Anonymous , Try measure like this for

flag and take min for value

Measure =
VAR __id = MAX ('Table'[Customer] )
VAR __date = CALCULATE ( Min('Table'[Value] ), ALLSELECTED ('Table' ), 'Table'[Customer] = __id )
return
CALCULATE ( Max ('Table'[Flag] ), VALUES ('Table'[Customer] ),'Table'[Customer] = __id,'Table'[Value] = __date )