return a value from another table based on the closest value of a column please help

[fingalbrad]

HI I have a table called JP Outputs

POF

0.2234

0.2344

0.3455

And another table called Pof per Day

DAYS                                                           

Days	PoF Today
1	0.28
2	0.34
3	0.456

What I want to do is grab the POF from JP outputs and return the days that corresponds to the closest PoF using PoF Today

please plase please help

[v-jianboli-msft · Accepted solution]

Hi @fingalbrad ,

Please try:

First, create a table visual of 'Pof per Day'

Then apply the measure to visual level filter:

Flag =
VAR _a =
    ADDCOLUMNS (
        ALL ( 'Pof per Day' ),
        "Diff",
            MINX (
                'JP Outputs',
                ABS ( [POF] - CALCULATE ( MAX ( 'Pof per Day'[PoF Today] ) ) )
            )
    )
RETURN
    IF (
        MAX ( 'Pof per Day'[Days] )
            = MINX ( FILTER ( _a, [Diff] = MINX ( _a, [Diff] ) ), [Days] ),
        1,
        0
    )

Final output:

Best Regards,

Jianbo Li

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