Lookup value the first one when have more than 1 single value

[jeongkim]

Hi,

Can we lookupvalue the first one? when there are more than 1 single value getting an error?

[Anonymous]

Hi, @jeongkim 

Can you provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

[ryan_mayu]

@jeongkim 

it's because the coding gets different results , so can't decide which one to use.

you can try use max OR min

=maxx(filter('5G SVC_MASTER SINCE 2024', 5G SVC_MASTER SINCE 2024'[Site]='Distinct - Master Data'[Internal Site ID]),'5G SVC_MASTER SINCE 2024'[GR Date]))

[jeongkim]

it doesn't meet my query

[johnt75]

Its a bit more convoluted, and you would need a uniquely identifying column or columns in the target table which you could sort by. If you don't have a unique column already then you could use Power Query to add an index column and use that.

You could then create a calculated column like

My Value =
VAR AllEntries =
    SELECTCOLUMNS (
        FILTER (
            ALL (
                'Target Table'[Target Value],
                'Target Table'[Lookup value],
                'Target Table'[Order by column]
            ),
            'Target Table'[Lookup value] = 'Source Table'[Lookup value]
        )
    )
VAR FirstEntry =
    TOPN ( 1, AllEntries, 'Target Table'[Order by column], ASC )
VAR Result =
    SELECTCOLUMNS ( FirstEntry, 'Target Table'[Target Value] )
RETURN
    Result

where [Target Value] is the column you are trying to retrieve and [Lookup Value] is the column which matches in both tables. You could extend this pattern to include multiple lookup values and multiple sort columns as necessary.

[jeongkim]

Not working,, pls help fix the code. 

GR Date =

VAR AllEntries =

    SELECTCOLUMNS (

        FILTER (

            ALL (

                'GR - 5G SvC_master since 2023 - Current Monthly Data'[GR Date],

                'GR - 5G SvC_master since 2023 - Current Monthly Data'[Site],

                'GR - 5G SvC_master since 2023 - Current Monthly Data'[Index],

            ),

            'GR - 5G SvC_master since 2023 - Current Monthly Data'[Site] = 'Distinct - Master Data'[Internal Site ID]

        )

    )

VAR FirstEntry =

    TOPN ( 1, AllEntries, 'GR - 5G SvC_master since 2023 - Current Monthly Data'[Index], ASC)

VAR Result =

    SELECTCOLUMNS ( FirstEntry, 'GR - 5G SvC_master since 2023 - Current Monthly Data'[GR Date] )

RETURN

    Result

[johnt75]

Remove the comma  on line 8.

[jeongkim]

Still not working

[johnt75]

That's my fault, the error is in the code I posted.

You don't need the SELECTCOLUMNS in the AllEntries variable. Delete that line and the associated brackets and it should be fine.

[jeongkim]

Can you help me write a full code?

Target value has 'Index' column which I would like to get latter index number's 'GR Date' when values has more then 1 single value. 

Table name(data source): GR - 5G SvC_master ~ 2022 - Previous Backup Data

Column name(target value): GR Date

Column name for lookup value: Site

Table name: Distinct - Master Data

Column name for lookupvalue(destination): GR Date

Column name for lookup value: Internal Site ID